mysQL Query help regarding find text

Question

Hello,

I need to get all rows in which eventname field contains this text (including single quote)

's Birthday

I tried this but it is giving error:

select * from fab_scheduler where eventname like '%\'s Birthday%'

How do i construct such query?

Thanks

Answer 1

Either use double quotes, or escape the single quote.

select * from fab_scheduler where eventname like "%'s Birthday%"

select * from fab_scheduler where eventname like '%''s Birthday%'

Answer 2

select * from fab_scheduler where eventname like "%'s Birthday%"

Answer 3

If you intend to include 's in your SQL, you need to escape it:

There are several ways to include quote characters within a string:

• A “'” inside a string quoted with “'” may be written as “''”.
• A “"” inside a string quoted with “"” may be written as “""”
• Precede the quote character by an escape character (“\”)
• A “'” inside a string quoted with “"” needs no special treatment and need not be doubled or escaped. In the same way, “"” inside a string quoted with “'” needs no special treatment.

Additionally, if you are constructing the SQL in a program, you should strongly consider escaping variables that go into SQL queries by using mysql_real_escape_string or its equivalent in your programming language. Failure to do so will make your application vulnerable to SQL injection attacks by whomever controls the data source for said variables (probably your users; keep in mind that "your users" can mean "the whole internet" depending on circumstances).

Edit: Quoted the dev.mysql.com instructions above.