CODEWITHSUNDEEP
phpmysqlsqlsql-like
Mossawi
Mossawi

Reputation: 911

Get exact match with LIKE - SQL/PHP

Given the text:

"Hello, I'm from Hell",
"Hello, I'm from Ell"

and the following SQL clause:

SELECT * FROM table WHERE text LIKE '%ell%'

I get both the texts above, but I don't want to get both texts, because I was looking for the text "Ell" and not "Hell"

If anyone knows what I mean, can you help me out?

Thanks in advance!

EDIT:

BETTER EXAMPLE

Like when you want to look for the word 'big' but it can't be part of any other word like 'bigger' or 'biggest'

Upvotes: 3

Views: 8703

Answers (5)

user8031209
user8031209

Reputation:

you can just use REGEXP in the query. have a look a this resource which contains all the detail you need.

syntax is simple as follows

$sql= "select * from post where title REGEXP '$s'";

Upvotes: 0

Telvin Nguyen
Telvin Nguyen

Reputation: 3559

Select the exactly match no case sensitive

SELECT * FROM table where LOWER(column) like BINARY LOWER('ell')

It works if the text has multi-line.

Upvotes: 0

Paul
Paul

Reputation: 141829

You could use the MySQL Regex word boundary matching:

SELECT * FROM table WHERE text REGEXP '[[:<:]]ell[[:>:]]'

This matches a string which cosntains the word ell with a word boundary on either side.

Upvotes: 9

asim-ishaq
asim-ishaq

Reputation: 2220

You can try this:

SELECT * FROM table WHERE text LIKE BINARY '%ell%'

Upvotes: 1

Matt Busche
Matt Busche

Reputation: 14333

Can you search for a space as well?

SELECT * 
FROM table 
WHERE text LIKE '% ell%'
OR text LIKE 'ell%' // needed if string starts with ell

Upvotes: 1

Related Questions