How to select records that have apostrophe containers using the same words without apostrophe words?-CodePudding

How to select records that have apostrophe containers using the same words without apostrophe words?

 ---- ------------------------------------------ ------- 
| id | name                                     | price |
 ---- ------------------------------------------ ------- 
|  1 | World's Best Inspirational Books         | 199   |
|  2 | Ben Martin Men's Slim                    | 98    |
|  3 | Harry Potter and the Philosopher's Stone | 79    |
|  4 | Today's life                             | 690   |
|  5 | Life's Amazing Secrets                   | 99    |
 ---- ------------------------------------------ -------

$search = "worlds";

SELECT * FROM product WHERE name LIKE "%$search%";

I want this result from $search = "world's";

 ---- ------------------------------------ ------- 
| id | name                               | price |
 ---- ------------------------------------ ------- 
|  1 | World's Best Inspirational Books   | 199   |
 ---- ------------------------------------ -------

But I got this, Empty set (0.001 sec)

How can I able to search world's(apostrophes) words (without including apostrophe them) using `worlds?

CodePudding user response：

You can escape an apostrophe by doing:

World''s

In a program, a "dirty" way to fix it, is by adding an additional apostrophe ( ' ) to all instances within a parameter, before calling the query.

CodePudding user response：

I don't know if this works in mysql, but in sql server you can do this

declare @test table (name varchar(50))
insert into @test values ('World''s Best Inspirational Books'), 
                         ('Ben Martin Men''s Slim'),
                         ('Harry Potter and the Philosopher''s Stone'),
                         ('Today''s life'),
                         ('Life''s Amazing Secrets')

select * 
from   @test t
where  t.name like '%world_s%'

and it will return the first row.
So if you don't want to add extra ' in your search, maybe you can replace all ' with an _