"Where" statement: match a single word (not substring)

Question

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.

Answer 1

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.

Answer 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