CODEWITHSUNDEEP
sqlselect
Ali Sheikhpour
Ali Sheikhpour

Reputation: 11121

sql select where column value exists in a text

I'm looking for reverse of like operator. I have long text and a sql table of words. I want to select words which exist inside the text. Something like this:

select word from mytable where word in ('this is a long story')

Upvotes: 1

Views: 887

Answers (4)

kofemann
kofemann

Reputation: 4423

This can be DB depended, for example with postgreSQL you can do something like this:

select word from mytable where word  in (select regexp_split_to_table('this is a long story', E'\\s+') );

Other databases do have a similar functions.

Upvotes: 1

Serkan Arslan
Serkan Arslan

Reputation: 13403

you can use CHARINDEX

select word from mytable where CHARINDEX(word , 'this is a long story') > 0

Upvotes: 1

Egalitarian
Egalitarian

Reputation: 2228

SELECT word
FROM mytable
WHERE INSTR('this is a long story', word) > 0

The function INSTR is multibyte safe, and is case sensitive only if at least one argument is a binary string. Hope it helps.

Upvotes: 1

melpomene
melpomene

Reputation: 85897

select word from mytable where 'this is a long story' like '%' || word || '%'

Do you mean like this?

Upvotes: 0

Related Questions