SQL Select where column contains 1 value in set of words-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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