How do I find rows that contain two words "First" "Second" regardless sequence-CodePudding

#360Search #SearchAll #SearchContain #SearchEverything #SQL #SELECT

How do I find rows that contain two words "First" "Second" regardless of in sequence or not? looking for a single where condition to cover this case.

Here is MyTable:

---------------------------
RowNo, MyStringColumn
---------------------------
1 , 'First Second Third'
2 , 'Second Third First'
3 , 'Third First Second'
4 , 'First Third Second'
5 , 'Second Second Third'

Expected Result: Row#1,2,3,4

---------------------------
RowNo, MyStringColumn
---------------------------
1 , 'First Second Third'
2 , 'Second Third First'
3 , 'Third First Second'
4 , 'First Third Second'

below SQL returns Row#1,3,4 and doesn't return Row#2 due to an unordered sequence.

SELECT * FROM MyTable WHERE MyStringColumn like '%First%Second%

Here is the SQL script to generate MyTable and data

DROP TABLE IF EXISTS [#MyTable];
SELECT [RowNo],[MyStringColumn] INTO [#MyTable]
FROM (SELECT (1)[RowNo], ('First Second Third')[MyStringColumn]
       UNION SELECT 2, 'Second Third First'
       UNION SELECT 3, 'Third First Second'
       UNION SELECT 4, 'First Third Second'
       UNION SELECT 5, 'Second Second Third'
)t

SELECT * FROM [#MyTable] WHERE [MyStringColumn] LIKE '%First%Second%'

CodePudding user response：

Just LIKE twice.

SELECT *
FROM MyTable
WHERE MyStringColumn LIKE '%First%' 
  AND MyStringColumn LIKE '%Second%'

And if you want to make sure it's 2 seperate words

SELECT *
FROM MyTable
WHERE ' ' MyStringColumn ' ' LIKE '% First %' 
  AND ' ' MyStringColumn ' ' LIKE '% Second %'

CodePudding user response：

string_split based approach. Useful when you want to check many words:

select *
from t
where exists (
    select 1
    from string_split(t.MyStringColumn, ' ')
    where value in ('first', 'second')
    having count(distinct value) = 2 -- this should match the number of items in the "in" clause
)