Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
Hi,
 
i want to query on 2 lists finding in column 'A1' from Table 'A' with the 2 columns viz. B1, B2 in Table 'B'. Query should be using "LIKE" operator where Column A1 like ('%B1%') OR Column A1 like ('%B2%').
 
current result gives me 2 likes for single record, which is correct in its place because Column matched from multiple rows.
 
Here is i want...
If column A1 matched from B1 OR B2 then should skip looking for further matches. it should not look in other rows of table B. Also i want to mark that the column matched with B1 or B2.
 

 

Thanks
 
Pinank Shah
Posted 30-Apr-13 0:01am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

It will not stop searching if one match found but you can filter records so, finally you will get result you want
Try this...
with tbl1 as
        (
            select 'Abc' as A1
            union all
            select 'xyz' as A1
        )
select * from
(
    select Row_Number() over(partition by A1 order by A1) SrNo,* from tbl1
    left join
        (
            select 'b' as B1, 'A' as B2
            union all
            select 'a' as B1, 'x' as B2
        ) as tbl2 on A1 like '%' + B1 + '%' or A1 like '%' + B2 + '%'
) as ftbl where srno=1
Happy Coding!
Smile | :)
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

I'll walk through this solution bit by bit ...
Quote:
Query should be using "LIKE" operator where Column A1 like ('%B1%') OR Column A1 like ('%B2%')
.
Could be solved with
SELECT * FROM A INNER JOIN B ON A1 LIKE '%' + B2 + '%' OR A1 LIKE '%' + B1 + '%'
Quote:
If column A1 matched from B1 OR B2 then should skip looking for further matches

Use something like
SELECT TOP 1 * FROM A ...
which will stop once it has found a single matching row. Note that you can use this in conjunction with ORDER BY ... to refine it's behaviour.
Quote:
Also i want to mark that the column matched with B1 or B2
.I'm going to identify which column we used (B1 or B2) using a CASE statement like this
CASE WHEN CHARINDEX(B2, A1) > 0 THEN 'B2' ELSE 'B1' END AS WHICH
 
To pull of this together we're first going to identify the single matching row on table A with a sub-query and I'm going to call the results of that sub-query TEMPRES, then we're going to join A to TEMPRES to update the relevent row.
 
In the full example below I've assumed that you have some way of uniquely identifying each row in table A (this is usually a very good idea - have a look at the IDENTITY(s,i) property for a column). In my example I've called this ID
UPDATE A
SET A1 = TEMPRES.WHICH
FROM A
INNER JOIN
    (SELECT TOP 1 A.ID, CASE WHEN CHARINDEX(B2, A1) > 0 THEN 'B2' ELSE 'B1' END AS WHICH
        FROM A INNER JOIN B ON A1 LIKE '%' + B2 + '%' OR A1 LIKE '%' + B1 + '%' ) TEMPRES ON A.ID=TEMPRES.ID
Finally, you might find these references helpful
http://www.akadia.com/services/sqlsrv_subqueries.html[^]
http://msdn.microsoft.com/en-us/library/ms186775.aspx[^]
 
[Edit - for some reason I started using column A2 instead of A1 ... corrected the sql]
  Permalink  
v2
Comments
Pinank_CD at 1-May-13 5:20am
   
Below is a scenerio where i have to write 2 queries for desired result. how can i write that in single query?
---------------------------------------------------------------------------------------
 
CREATE TABLE #tblTestSearchDetails (
UploadDetailsId int IDENTITY,
Up_Amount decimal(18,2),
Up_CCY varchar(8),
Up_ExtRef varchar(100),
Up_CustRef varchar(100),
Up_Status varchar(100)
)
 
insert into #tblTestSearchDetails(Up_Amount,Up_CCY,Up_CustRef,Up_ExtRef,Up_Status)values(300.00,'GBP','0000','EREF0000REMIashish222222-025','Amount')
insert into #tblTestSearchDetails(Up_Amount,Up_CCY,Up_CustRef,Up_ExtRef,Up_Status)values(300.00,'GBP','0000','EREF0000REMIashish222222-026','Amount')
insert into #tblTestSearchDetails(Up_Amount,Up_CCY,Up_CustRef,Up_ExtRef,Up_Status)values(300.00,'GBP','0000','EREF0000REMIPinank222222-030','Amount')
insert into #tblTestSearchDetails(Up_Amount,Up_CCY,Up_CustRef,Up_ExtRef,Up_Status)values(300.00,'GBP','0000','EREF0000REMIpinank999999','Amount')
insert into #tblTestSearchDetails(Up_Amount,Up_CCY,Up_CustRef,Up_ExtRef,Up_Status)values(400.00,'GBP','0000','EREF0000REMIrobert785478-007','Amount')
 

CREATE TABLE #tblTestLookUpDetails (
T_FundInId int IDENTITY, T_ClientNo varchar(100), T_ContractNo varchar(100),
T_CCY varchar(100), T_Name varchar(100), T_Amount decimal(18,2)
)

insert into #tblTestLookUpDetails(T_Amount,T_CCY,T_ClientNo,T_ContractNo,T_Name)values(300.00,'GBP','222222','222222-025','ashish')
insert into #tblTestLookUpDetails(T_Amount,T_CCY,T_ClientNo,T_ContractNo,T_Name)values(300.00,'GBP','222222','222222-026','ashish')
insert into #tblTestLookUpDetails(T_Amount,T_CCY,T_ClientNo,T_ContractNo,T_Name)values(300.00,'GBP','222222','222222-028','ashish')
insert into #tblTestLookUpDetails(T_Amount,T_CCY,T_ClientNo,T_ContractNo,T_Name)values(300.00,'GBP','999999','999999-030','pinank')
insert into #tblTestLookUpDetails(T_Amount,T_CCY,T_ClientNo,T_ContractNo,T_Name)values(300.00,'GBP','999999','999999-031','pinank')
 

create table #tblTestPhubRelation
(
R_RelId int IDENTITY,
R_UploadDetailId int,
R_FundsIn int,
R_DealNo varchar(20),
R_Name varchar(20),
R_Match varchar(20),
)
 
--Query:1
insert into #tblTestPhubRelation(R_UploadDetailId,R_FundsIn,R_DealNo,R_Match)
select U1.UploadDetailsId,T1.T_FundInId,T1.T_ContractNo,'Contract Match' from #tblTestSearchDetails U1, #tblTestLookUpDetails T1
where (U1.Up_ExtRef LIKE '%'+ T_ContractNo +'%' --or U.Up_ExtRef LIKE '%'+ T_ClientNo +'%'
or U1.Up_CustRef LIKE '%'+ T_ContractNo +'%')
 
--Query:2
insert into #tblTestPhubRelation(R_UploadDetailId,R_FundsIn,R_DealNo,R_Match)
select U1.UploadDetailsId,T1.T_FundInId,T1.T_ContractNo,'Client Number Match' from #tblTestSearchDetails U1, #tblTestLookUpDetails T1
where (U1.Up_ExtRef LIKE '%'+ T_ClientNo +'%' OR --or U.Up_ExtRef LIKE '%'+ T_ClientNo +'%'
U1.Up_CustRef LIKE '%'+ T_ClientNo +'%')
and U1.UploadDetailsId not in( select R_UploadDetailId from #tblTestPhubRelation)
 

select * from #tblTestPhubRelation
 

drop table #tblTestPhubRelation
drop table #tblTestSearchDetails
drop table #tblTestLookUpDetails

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 605
1 Maciej Los 319
2 BillWoodruff 184
3 Richard MacCutchan 180
4 Suraj Sahoo | Coding Passion 180
0 OriginalGriff 8,654
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,639
3 Maciej Los 5,229
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 30 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100