CODEWITHSUNDEEP
sqlselect
hvtanh07
hvtanh07

Reputation: 9

SQL Select where column contains 1 value in set of words

I need a select which would return row if column A of that row contains any word from a list of words which get from user input

SELECT * 
FROM MyTable 
WHERE ColumnA CONTAINS ANY 'list of word'

Since the list of words has an unknown number of words, I store the whole list in the same string. each word can be separated with "_", "-" or white space.

Upvotes: 0

Views: 991

Answers (2)

hvtanh07
hvtanh07

Reputation: 9

I've came up with this solution:

SELECT *
from TableA tb 
RIGHT JOIN STRING_SPLIT ( 'list of words' , 'seperator' ) v on tb.ColumnA = v.value
WHERE tb.ColumnA IS NOT NULL

Upvotes: 0

Sayandip Ghatak
Sayandip Ghatak

Reputation: 303

You can try something like this if you are using oracle :

SELECT * FROM MyTable WHERE ColumnA in (select upper(regexp_substr('word1- 
word2-word3','[^-]+',1,level)) from dual
connect by upper(regexp_substr('word1-word2-word3','[^-]+',1,level)) is 
not null)

If you are using "_" then replace the hyphen with underscore is regexp_substr parameter.

Upvotes: 1

Related Questions