CODEWITHSUNDEEP
sql-server
soccer7
soccer7

Reputation: 4005

n number of LIKE in a SQL statement

Suppose I have a table list with a list of records like

list_Name
A1
A2
A3
A4

Another table dest contains a column having a long string

ID  String
1   A1 This is line 1 A2
2   A3 Line number 2
3   A5 Line 3

Using values in list table, I want to find a record from dest table

What I know is fetching them one by one. Example-

SELECT * FROM dest WHERE string LIKE '%A1%' OR string LIKE '%A2%'

But how I can I use LIKE for every value in list table?

list table can have n number of records.

Upvotes: 2

Views: 61

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93724

You can use JOIN/EXISTS to do check every value in LIST table.

I usually prefer EXISTS for checking the existence. 

SELECT * 
FROM dest d 
WHERE exists (select 1 from list l where d.string LIKE '%'+list_Name+'%')

To resolve Collation conflict we can use explicit collation 

SELECT * 
FROM   dest d 
WHERE  EXISTS (SELECT 1 
               FROM   list l 
               WHERE  d.string COLLATE database_default LIKE 
                      '%' + list_name + '%' COLLATE database_default)

Upvotes: 2

Related Questions