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 but that question has no SQL answer — Sebastian
– Sebastian, Commented Apr 22, 2015 at 19:44
You can reverse the LIKE operator: WHERE @MyString LIKE '%'+[Word]+'%' — Tab Alleman
– Tab Alleman, Commented Apr 22, 2015 at 19:45
should be SQL agnostic (in fact I am using Java, querydsl, jpa and currently mysql, which might change later) — Sebastian
– Sebastian, Commented Apr 22, 2015 at 19:46

Rahul Tripathi · Accepted Answer · 2015-04-22 19:58:52Z

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, 2015 at 19:58

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tab Alleman · Accepted Answer · 2015-04-22 19:49:02Z

1

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

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

answered Apr 22, 2015 at 19:49

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

2 Comments

Gordon Linoff Over a year ago

Your syntax is not MySQL-compliant.

Tab Alleman Over a year ago

Ok, I don't know MySQL syntax, but would the principle work in MySQL?

Collectives™ on Stack Overflow

Find strings within text in SQL

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related