ways to escape single quotes in sql LIKE command

Question

hi,

i want to know what are the various ways to ESCAPE single quotes(') in SQL LIKE command .

one way is to put two single quotes whenever you have to escape a single quote.

Can you people suggest something??

Thanks.

Database -- SQL2005, oracle 10g

Answer 1

You already have the answer. You have to use two single quotes:

select * from table where field like '%''something''%'

Answer 2

At first, the best way is to bind the parameter with ADO or ADO.Net

Like (example in C# with ADO.Net):

SqlCommand x = new SqlCommand(sqlConnection, @"select * from table where col like '%'+@para1+'%'");
x.parameters.add(new SqlParameter("@para1",sqltype.varchar,100)).value = "this is a' test";

In MSSQL 2005 you escape a single quote (') with double single quote ('') if you not whant to bind.

select * from table where col like '%this is a'' test%'

Answer 3

Two single quotes is the best solution.

Alternatively, you can use a CHAR(39) to represent a single quote character.

UPDATE Employee SET LastName = 'O' +  CHAR(39) + 'Brien' 
WHERE ID=1;

Answer 4

There is also the "Q-quote" method:

select * from mytable where text like q'#%Here's some text%#';

This is available since Oracle 10.2.

I used a '#' character as the quote delimiter, but you can use pretty much any character that won't appear in the string (there are a few exceptions, such as the space character).

In a simple example like that above I probably wouldn't do this, I'd just double=up the single quotes, but it does come in handy when building large dynamic SQL statements that include lots of string literals.