views:

492

answers:

7
+2  Q: 

SQL Escape ' '

Hi

I am trying to run a query in SQL 2008 by doing:

@query varchar(max)

SET @query = 'SELECT * FROM Table WHERE [Name] = ' 'Karl' ' '

EXEC(@query)

The problem is that for some reason the apostrophes around 'Karl' don't get escaped, i.e. the query executes as ...WHERE [Name] = Karl and fails.

Anyone have a suggestion?

Thanks

Karl

A: 

do like this SET @query = 'SELECT * FROM Table WHERE [Name] = ''''Karl'''''

KuldipMCA
this will not work, unless Karl is stored in the database with one single quote before and after his name like 'Karl' because it results in the actual query being: SELECT * FROM Table WHERE [Name] = ''Karl''
KM
thne add one more Quote at the enddo like this SET @query = 'SELECT * FROM Table WHERE [Name] = ''''Karl'''''' like this
KuldipMCA
A: 

Simply escape the apostrophes by using the escaping bar \ like this 'SELECT * FROM Table WHERE [Name] = ' \'Karl\' ' '

Hope it helps

Carlos
that will not work in sql server's t-sql
KM
+2  A: 

This works on my machine from SQL Server Management Studio:

@query varchar(max)

SET @query = 'SELECT * FROM Table WHERE [Name] = ''''''Karl'''''''

EXEC(@query)
Manga Lee
this will not work, unless Karl is stored in the database with two single quotes before and after his name like ''Karl'' because it results in the actual query being: SELECT * FROM Table WHERE [Name] = '''Karl'''
KM
+2  A: 

There are several ways that you can escape character data in SQL Server, some people even advocate the use of the QUOTENAME() functions.

If you really want to develop of solid understanding of this subject area then may I recommend that you take a look at what experienced SQL Server Developers consider to be essential reading with regard to the different methods you can use to incorporate Dynamic T-SQL into your coding.

The Curse and Blessings of Dynamic SQL

John Sansom
Same place I point people towards.
dverespey
A: 

Try:

DECLARE @query varchar(max)

SET @query = 'SELECT * FROM Table WHERE [Name] = ''Karl'''

PRINT 'when in doubt, print the query out: '+ISNULL(@query,'')
EXEC(@query)

To have a single quote appear, you need to have two adjacent single quotes. You escape a single quote with a single quote, for example:

PRINT ''''     --will print a one single quote
PRINT ''''''   --will print two single quotes
PRINT 'can''t' --will print can't
KM
A: 

A double single ('') quote will act like a single single quote when inside a string literal.

Have you tried using a variable?

declare @karl_name varchar(10);
set @karl_name = '''Karl''';

SELECT * FROM Table WHERE [Name] = @karl_name
Matt Brunell
A: 

This works:

create table #demo([Name] varchar(max))
insert into #demo([Name]) values('''Karl''')
insert into #demo([Name]) values('Karl')
declare @query varchar(max)
set @query = 'SELECT * FROM #demo WHERE [Name] = ''''''Karl'''''''
EXEC(@query)

Output:

'Karl'

But if 'Karl' is variable text, it's highly recommended to use something like this instead:

declare @query nvarchar(max)
declare @param varchar(max)
set @param = N'''Karl'''
set @query = N'SELECT * FROM #demo WHERE [Name] = @param'
exec sp_executesql @query, N'@param varchar(max)', @param
BlueMonkMN