CODEWITHSUNDEEP
sqlsql-server
Karthik S
Karthik S

Reputation: 11548

How to use Like operator to match a string in one table to a sub-string into another table

I have to two tables Table 1 & Table 2. Table 1 has customer details and Table 2 has transaction details:

    Table 1                      Table 2        

Col1    CustCode        Col1    TrainsactionID      Col3
11      AAA             101     2139812/BBB/98waw   EDF
22      BBB             102     2398472/CCC/18sdf   QWD
33      CCC             103     9283743/AAA/76afa   VFB
44      DDD             104     2983472/BBB/123as   FGV

I need to select the customer codes (strings) AAA, BBB and CCC from column "CustCode" of table 1 as they exist as a substring in column "TransactionID" of table 2. Is there a way I can write a correlated subquery using 'Like %' operator to fetch all such matching CustCodes?

Upvotes: 1

Views: 88

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

You may use a join, on the condition that the customer code from the first table appears in the path of the transaction ID from the second table.

SELECT t1.Col1, t1.CustCode, t2.Col1, t2.TrainsactionID, t2.Col3
FROM Table1 t1
INNER JOIN Table2 t2
    ON t2.TrainsactionID LIKE '%/' + t1.CustCode + '/%';

enter image description here

Demo

Note that this join condition is not optimal and probably can't use an index. If you have a long term need for these tables, then consider setting up join columns which can use equality operators.

Upvotes: 5

Fahmi
Fahmi

Reputation: 37493

You can try below -

    select A.col1, a.custcode,b.transactionid,b.col3
    from table1 a inner join table2 b
       on a.CustCode like concat('%',b.TrainsactionID,'%')

Upvotes: 1

Related Questions