0

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"

ariel
  • 2,962
  • 7
  • 27
  • 31
  • Do you store multiple tags in `description_field`? – juergen d Jan 14 '14 at 21:52
  • @juergend yes so a person can have multiple tags in their description. – ariel Jan 14 '14 at 21:53
  • possible duplicate of [Search for "whole word match" in MySQL](http://stackoverflow.com/questions/656951/search-for-whole-word-match-in-mysql) – Carth Jan 14 '14 at 21:53
  • 3
    Never, never, never store multiple values in one column! Normalize your data. – juergen d Jan 14 '14 at 21:54
  • [Duplicate of this problem solved long ago](http://stackoverflow.com/questions/10252513/whats-the-correct-sql-command-to-find-exact-matches) – Mike Jan 14 '14 at 21:55
  • 1
    @juergend I don't think he's storing the hash-tags as tags, just as part of the description - i.e. the user has typed "I had so much fun #whatever" – Blorgbeard Jan 14 '14 at 21:56
  • @Blorgbeard yes that's right. – ariel Jan 14 '14 at 21:58
  • 1
    I agree with @juergend, this is not a workable design the way you want to query it. Even if you get the query to work correctly it will be much less efficient than a query on correctly normalized and indexed table. – HLGEM Jan 14 '14 at 21:59
  • This question might get closed as being a duplicate, but even though the other question answers your specific question, I agree with the other comments here that this is not how you should be doing it. – Mike Jan 14 '14 at 22:51

4 Answers4

2

Use RLIKE, the regex version of LIKE:

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

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

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • thanks i tried using it but i got no returned rows. I know for sure I have at least 20,000 rows that have that hashtag. – ariel Jan 14 '14 at 21:59
  • This should work, so without seeing sample data I can't help. Please create an [SQLFiddle](http://sqlfiddle.com) with some sample data and post the link into the question, let me know you've done it and I'll get it working. – Bohemian Jan 14 '14 at 22:37
1

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

Gert B.
  • 2,282
  • 18
  • 21
0

Something like this might work:

select id from table 
where (' ' + description_field + ' ') LIKE "% #whatever %" and user_id=333
Blorgbeard
  • 101,031
  • 48
  • 228
  • 272
0

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.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43