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

Possible duplicate of [Match only entire words with LIKE?](http://stackoverflow.com/questions/6283767/match-only-entire-words-with-like) — ch271828n, Oct 02 '16 at 10:23

score 1 · Accepted Answer · answered Oct 02 '16 at 10:22

1

The main idea is: match the space too.

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

answered Oct 02 '16 at 10:22

ch271828n

15,854
5
53
88

Thanks but I am afraid it will also match occurrences of that word in other! Is there a maybe regular extension for it. If not 100% match skip? – letsforum Oct 02 '16 at 10:26
@letsforum sorry but can't follow you. Can you give an example explaining what you mean? – ch271828n Oct 02 '16 at 10:29
Sorry buddy. I mean if I use this: col LIKE '% doc %' OR col LIKE '% doc' OR col LIKE 'doc %' OR col = 'doc' will it match 100% the entire word and skip matching occurrences of that word in others? LIKE '% doc' will return words that start with my word to match etc. – letsforum Oct 02 '16 at 10:32
@letsforum No I don't think so. `% doc` only matchs `abcd[space]doc`, not `abcddoc` neither `abc docefg` – ch271828n Oct 02 '16 at 10:33
@letsforum for further information, see mysql document. – ch271828n Oct 02 '16 at 10:33
Sorry I am only scratching the surface on PHP and SQL. Thanks gonna try it! – letsforum Oct 02 '16 at 10:36
You're welcome :) If it works, then you can select me as the best answer. – ch271828n Oct 02 '16 at 10:36

score 0 · Answer 2 · answered Oct 02 '16 at 11:32

0

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.

answered Oct 02 '16 at 11:32

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 3 · edited May 17 '20 at 00:00

0

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.

edited May 17 '20 at 00:00

halfer

19,824
17
99
186

answered Oct 02 '16 at 12:25

Jim Macaulay

4,709
4
28
53

Hope you got it – Jim Macaulay Oct 02 '16 at 12:25

SQL Match individual word?

3 Answers3