MySQL exact word existence check in a table Field

Question

I've a Query. Is there any built-in function or any other method associated with MySQL, which will return rows that contains EXACT word in the database table field? I'm aware that with MySQL LIKE operator, You can search for a specified pattern in a column which will match a string value against a pattern string containing wild-card characters. But With MySQL LIKE clause, It'll return substring entries too.

Eg.

Suppose 3 column values are like below:

1. "Take another shot of courage"

2. "The End of This age is Not Yet"

3. "Employers can obtain this wage rate by submitting a request"

I want to retrieve all rows in which column value contains the exact word 'age'

If we prepare query like this

SELECT * FROM sometable WHERE somefield LIKE '%age%'

It'll return all three rows. But my intention is only to get the second row. How do we achieve this? Thanks In Advance.

Cheers,
JENSon.

Answer 1

Use this query

SELECT * FROM sometable WHERE somefield LIKE '% age %' 
OR somefield LIKE 'age %' OR somefield LIKE '% age'

Answer 2

Try this

  1 SELECT * FROM sometable WHERE somefield LIKE '% age %' to search in middle of string

2 SELECT * FROM sometable WHERE somefield LIKE '% age' if word is at end of string

3 SELECT * FROM sometable WHERE somefield LIKE 'age %' if your word is at starting of the string 

Answer 3

You can use MySQL's regex engine and match the word boundary characters [[:<:]] and [[:>:]]

This will match age but not courage or wage or aged. It will also match the age in -age- or any other row where the text age is surrounded by non word characters. This means it will match age at the beginning and end of the string.

It is a little less complicated to read in my opinion than having three conditions. If returning matches like -age- is acceptable then you might consider this.

I have not tested the performance or portability of this query either so you might want to consider that as well.

SELECT * FROM sometable WHERE somefield RLIKE '[[:<:]]age[[:>:]]';