SQL Match individual word?

Question

I am using like statement: %word% but the problem is it also matches words that are inside other words.

How can I match words that are separate from the rest in the string like this. I wanna match word1 in these strings:

word1 word2 word3

word2 word1 word3

word2 word3 word1

And don't match this: word2word1

Answer 1

The main idea is: match the space too.

col LIKE '% doc %' OR col LIKE '% doc' OR col LIKE 'doc %' OR col = 'doc'

Answer 2

The simplest way to do this in generic SQL is like this:

where ' ' || col || ' ' like '% word1 %'

Note that the operator for string concatenation may vary among databases.

Answer 3

You can use INSTR function to get the string required exactly

WHERE column_name = INSTR(column_name,'word')

INSTR - Instring function matches the string provided in the function. Rather than using like operator, this function provides you required result. Also like operator searches all the possible combinations and takes more time compared to INSTR function. To increase performance INSTR function can be used.