CODEWITHSUNDEEP
mysqlsqldatabasesubquery
ariel
ariel

Reputation: 3072

How do I use the LIKE function in SQL but for an exact word?

I am new to SQL and having and issue. I want to delete from my database wherever somebody in the description column has the hashtag "#whatever". I was able to write the following query: 

select id from table where description_field LIKE "%#whatever%" and user_id=333

But if i use the LIKE function here it will delete wherever it matches #whatever but I fear that it might delete something where it has #whateverANDthis.

How can I write a query that deletes a row wherever it ONLY contains "#whatever" in the description and not other variations like "#whateverANDthis" or "#whateverORthis".

I want to delete where it says:

"I had so much fun #whatever"

but not:

"I had so much fun #whateverAndWhatever"

Upvotes: 0

Views: 109

Answers (4)

Dan Bracuk
Dan Bracuk

Reputation: 20804

For your current situation, your query has to cover three scenarios, first word, middle word, and last word. So your query might resemble this:

where user_id = 33
and
(
description_field like '%#whatever %' -- first word
or description_field like '% #whatever %'  -- middle word
or description_field like '% #whatever'  -- last word
)

Or, you could work on those regex proposals. They might be better.

Upvotes: 0

Bohemian
Bohemian

Reputation: 425023

Use RLIKE, the regex version of LIKE:

WHERE description_field RLIKE '[[:<:]]#whatever[[:>:]]'

The expressions [[:<:]] and [[:>:]] are leading and trailing "word boundaries".

Upvotes: 2

Gert B.
Gert B.

Reputation: 2362

It would be better to save them in multiple columns but

SELECT id FROM table WHERE decription_field REGEXP '[[:<:]]#whatever[[:>:]]' and user_id=333

could do the trick

Upvotes: 1

Blorgbeard
Blorgbeard

Reputation: 103467

Something like this might work:

select id from table 
where (' ' + description_field + ' ') LIKE "% #whatever %" and user_id=333

Upvotes: 0

Related Questions