12,406,528 members (27,859 online)
Rate this:
See more:
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 29-Apr-13 23:01pm

Rate this:

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!
Rate this:

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://msdn.microsoft.com/en-us/library/ms186775.aspx[^]

[Edit - for some reason I started using column A2 instead of A1 ... corrected the sql]
v2
Pinank_CD 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 (
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_FundsIn int,
R_DealNo varchar(20),
R_Name varchar(20),
R_Match varchar(20),
)

--Query:1
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
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 +'%')

select * from #tblTestPhubRelation

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

Top Experts
Last 24hrsThis month
 OriginalGriff 270 Karthik Bangalore 204 Richard MacCutchan 115 BillWoodruff 75 Afzaal Ahmad Zeeshan 75
 OriginalGriff 7,957 Karthik Bangalore 3,531 ppolymorphe 3,400 Richard MacCutchan 2,587 F-ES Sitecore 2,300