CODEWITHSUNDEEP
mysqlsql
Eddy
Eddy

Reputation: 3713

SQL like with additional characters

I'm doing a textual search on a MySql table and I want to find words that may end with certain characters.

Here's an example. The search is for the word clip. I want my query to find the word clip, or clip? but not the word clipping.

So far this is what I tried: 

select * from product where title like '%clip%'

also finds the word clipping, which is not good.

select * from product where title like '% clip %'

doesn't find clipping but also ignores the word clip when there's a comma right after it, clip, and that's also not good for me.

Is there a way to specify that the query would ignore letters, but include characters like , or . or ? or basically any other character I choose?

Upvotes: 2

Views: 118

Answers (2)

Joni
Joni

Reputation: 111389

You can use a regular expression that matches word boundaries:

... where title regexp '[[:<:]]clip[[:>:]]'

If you have a lot of data and find yourself doing lots of searches like this, it may be a good idea to create a full text index and then use match...against:

... where match(title) against ('clip');

Upvotes: 3

Dylan Su
Dylan Su

Reputation: 6065

Use REGEXP.

where title REGEXP '.* clip[,.? ].*'

The following is a demo.

mysql> select 'the paper clip is white' REGEXP '.* clip[,.? ].*';
+----------------------------------------------------+
| 'the paper clip is white' REGEXP '.* clip[,.? ].*' |
+----------------------------------------------------+
|                                                  1 |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select 'the paper clip? is white' REGEXP '.* clip[,.? ].*';
+-----------------------------------------------------+
| 'the paper clip? is white' REGEXP '.* clip[,.? ].*' |
+-----------------------------------------------------+
|                                                   1 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select 'the paper clipping is white' REGEXP '.* clip[,.? ].*';
+--------------------------------------------------------+
| 'the paper clipping is white' REGEXP '.* clip[,.? ].*' |
+--------------------------------------------------------+
|                                                      0 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select 'the paper clipis white' REGEXP '.* clip[,.? ].*';
+---------------------------------------------------+
| 'the paper clipis white' REGEXP '.* clip[,.? ].*' |
+---------------------------------------------------+
|                                                 0 |
+---------------------------------------------------+
1 row in set (0.00 sec)

Upvotes: 2

Related Questions