Find strings within text in SQL

Question

I have a table with a string column "word" and a rather large text. Now I want to find those words that appear within that text. So IMHO I need a reverse LIKE operator. Ist that possible in plain SQL (no stored procedures)?

Example: Finding words in rhymes

| ID | Word |
| 1  | star |
| 2  | moon |
| 3  | sun  |
| 4  | sky  |

Text: Twinkle, twinkle, little star, How I wonder what you are! Up above the world so high, Like a diamond in the sky.

==> should find ID 1: start, 4: sky

duplicates [Find strings in text](http://stackoverflow.com/questions/15175742/find-strings-in-text) but that question has no SQL answer — Sebastian, Apr 22 '15 at 19:44
You can reverse the LIKE operator: WHERE @MyString LIKE '%'+[Word]+'%' — Tab Alleman, Apr 22 '15 at 19:45
should be SQL agnostic (in fact I am using Java, querydsl, jpa and currently mysql, which might change later) — Sebastian, Apr 22 '15 at 19:46

score 1 · Answer 1 · answered Apr 22 '15 at 19:49

1

The LIKE operator IS reversible. This is all you need to do:

WHERE @MyString LIKE '%'+[Word]+'%'

answered Apr 22 '15 at 19:49

Tab Alleman

31,483
7
36
52

1

Your syntax is not MySQL-compliant. – Gordon Linoff Apr 22 '15 at 19:49
Ok, I don't know MySQL syntax, but would the principle work in MySQL? – Tab Alleman Apr 22 '15 at 19:50

score 1 · Accepted Answer · answered Apr 22 '15 at 19:58

1

Try like this:

select ID from tablename
where 'Twinkle, twinkle, little star, How I wonder what you are! Up above the world so high, Like a diamond in the sky.'
LIKE CONCAT('%', word, '%');

SQL FIDDLE DEMO

answered Apr 22 '15 at 19:58

Rahul Tripathi

168,305
31
280
331

Find strings within text in SQL

2 Answers2