CODEWITHSUNDEEP
sqlsql-serversql-like
Mayank Pathak
Mayank Pathak

Reputation: 3681

Like Operator for checking multiple words

I'm struggling for a like operator which works for below example

Words could be

MS004 -- GTER   
MS006 -- ATLT   
MS009 -- STRR   
MS014 -- GTEE   
MS015 -- ATLT

What would be the like operator in Sql Server for pulling data which will contain words like ms004 and ATLT or any other combination like above.

I tried using multiple like for example

where column like '%ms004 | atl%'

but it didn't work.

EDIT

Result should be combination of both words only.

Upvotes: 2

Views: 26355

Answers (3)

AlexK
AlexK

Reputation: 9927

;WITH LikeCond1 as (
SELECT 'MS004' as L1 UNION
SELECT 'MS006' UNION
SELECT 'MS009' UNION
SELECT 'MS014' UNION
SELECT 'MS015')
, LikeCond2 as (
SELECT  'GTER' as L2 UNION
SELECT 'ATLT' UNION
SELECT 'STRR' UNION
SELECT 'GTEE' UNION
SELECT 'ATLT'
)

SELECT TableName.*
FROM LikeCond1
    CROSS JOIN LikeCond2
    INNER JOIN TableName ON TableName.Column like '%' + LikeCond1.L1 + '%'
                        AND TableName.Column like '%' + LikeCond2.L2 + '%'

Upvotes: 1

peter.petrov
peter.petrov

Reputation: 39437

Seems you are looking for this.

`where column like '%ms004%' or column like '%atl%'`

or this

`where column like '%ms004%atl%'

Upvotes: 11

Nagaraj S
Nagaraj S

Reputation: 13474

Try like this

select .....from table where columnname like '%ms004%' or columnname like '%atl%'

Upvotes: 1

Related Questions