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.

If there will always be a space before and after age, why not use LIKE ' %age% ' — fizzy drink, Apr 05 '13 at 10:46
Place two space before and after age and see if works. I mean `% age %` — , Apr 05 '13 at 10:48
read about full text search, `% age %` isn't solution for me. — mkjasinski, Apr 05 '13 at 10:48
SELECT * FROM sometable WHERE somefield LIKE '% age %' will work for most cases except for "age something" and "something age" — Anthony Palmer, Apr 05 '13 at 10:49
@fizzy drink: i didnt see you already given the same idea. Sorry for duplication. But theorit!cally it should work. Thanks — , Apr 05 '13 at 10:50
@JensonMJohn Accept the answer which has solved your problem...:) — Yogesh Suthar, Apr 05 '13 at 11:18

score 6 · Accepted Answer · answered Apr 05 '13 at 10:46

6

Use this query

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

answered Apr 05 '13 at 10:46

Yogesh Suthar

30,424
18
72
100

score 6 · Answer 2 · answered Apr 05 '13 at 11:01

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[[:>:]]';

score 2 · Answer 3 · 2013-04-05T11:02:00.580

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

edited Apr 05 '13 at 11:02

answered Apr 05 '13 at 10:55

MySQL exact word existence check in a table Field

3 Answers3

Linked