4

I am using MySQL.

I have a car table in my database, and there is a name column in that table.

Suppose the name column of the table contain values:

 +----------+
 |   name   |
 +----------+
 | AAA BB   |
  ----------
 | CC D BB  |
  ----------
 | OO kk BB |
  ----------
 | PP B CC  |
  ----------

I would like to search the table where name column value contains word "BB" (not substring), What is the SQL command to achieve this ?

I know LIKE , but it is used to match a contained substring, not for a word match.

P.S.

My table contains large data. So, I probably need a more efficient way than using LIKE

The values in name column are random strings.

Please do not ask me to use IN (...) , because the values in that column is unpredictable.

Mellon
  • 37,586
  • 78
  • 186
  • 264
  • This line of your question confuses me, "...used to match a contained substring, not for a word match." – George Johnston Nov 16 '11 at 14:06
  • Sorry for my poor english. I mean I would like to query in the column where the value contains the word "BB". The "LIKE" is used to search for a substring not a word. For example LIKE will return also "cccBB" which is not expected. – Mellon Nov 16 '11 at 14:08

2 Answers2

5

Try this WHERE clause:

WHERE name LIKE '% BB %'
OR    name LIKE 'BB %'
OR    name LIKE '% BB'
OR    name = 'BB'

Note that this will not perform well if your table is large. You may also want to consider a full-text search if you need better performance.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
2

You can use the REGEXP operator in MySQL:

SELECT *
FROM car
WHERE name REGEXP '[[:<:]]BB[[:>:]]'

It will match BB if it occurs as a single word. From the MySQL manual:

  • [[:<:]], [[:>:]]

    These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_).

    mysql> SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]';   -> 1
mysql> SELECT 'a xword a' REGEXP '[[:<:]]word[[:>:]]';  -> 0
knittl
  • 246,190
  • 53
  • 318
  • 364
  • 1
    You could refer to http://stackoverflow.com/questions/656951/search-for-whole-word-match-in-mysql before just copying it. – dimme Nov 16 '11 at 14:09
  • Can you explain a bit of the meaning of [[:<:]] , or the whole regular expression? – Mellon Nov 16 '11 at 14:10
  • @Dimme: I haven't copied it … I'm seeing the other question/answer for the first time. – knittl Nov 16 '11 at 14:11
  • @Dimme, you're sure knittl copied it? Please enlighten me: how can you tell? – toon81 Nov 16 '11 at 14:11
  • Ok thanks for the explanation. Negative vote removed. @toon81 if you google the question this URL comes up first. It is a valid reason to believe that this was copied because the answer is very similar. – dimme Nov 16 '11 at 14:15
  • @Dimme: The answer was very simple. With such small entropy two questions will look similar very quickly. But when I wrote the answer, I didn't search for »mysql match single word«, but for »mysql manual regex« instead. – knittl Nov 16 '11 at 14:19
  • I am mostly concern about the performance issue, will the REGEXP be slow for performance? as my table is large. – Mellon Nov 16 '11 at 14:22
  • mellon, the regex itself looks fine. it's almost 100% certainly much faster than regexing with PHP. @Dimme I guess it's a decent reason for suspecting it. – toon81 Nov 16 '11 at 15:36