CODEWITHSUNDEEP
mysql
user8912201
user8912201

Reputation:

Search for specific words in mysql

select * from data WHERE name LIKE  '%Hi There%';

returns this:

Hi There John
Hi There
Man Hi There
Hi There Mate
Hi Theres Alice
Hi Theree Man

I only want Hi There John, Hi There Mate, Hi There and Man Hi There to be returned because they contain Hi There specifically. Hi Theres Alice and Hi Theree Man should not appear.

How can this be done, simply?

Upvotes: 0

Views: 71

Answers (3)

Fabian De La Pena Montero
Fabian De La Pena Montero

Reputation: 11

You can use regular expressions to get exactly the rows with the values you want. A query that gives you what you need would look something like this:

select * from data WHERE name REGEXP '{^\bHi There\b\s*.*$}';

Upvotes: 0

Barmar
Barmar

Reputation: 780861

Use a regular expression instead of LIKE. Then you can use [:<:] and [:>:] to match word boundaries.

select * from data WHERE name RLIKE  '[:<:]Hi There[:>:]';

Upvotes: 0

Enrico Dias
Enrico Dias

Reputation: 1487

If I understand correctly, this should work:

SELECT * FROM data WHERE
    name LIKE 'Hi There %' OR
    name LIKE '% Hi There %' OR
    name LIKE '%Hi There';

Upvotes: 2

Related Questions