Single quote in NVARCHAR string?

How do I get a single quote (') in a NVARCHAR string in MS SQL Server?

e.g. SELECT @strsql = "SELECT * FROM tblTest WHERE Field1 Like 'blah''

Obviously this is invalid as the single quote before "blah" would end the
varchar string.

How do I get round this?

You have to double quote that, or use the CHAR expression:

SELECT '''SELECT * FROM tblTest WHERE Field1 Like ''blah'''''
OR
SELECT CHAR(39) + 'SELECT * FROM tblTest WHERE Field1 Like ' + CHAR(39)
+ 'blah' + CHAR(39) + CHAR(39)


HTH, Jens Suessmeyer.

Use two single quotes - see "Using char and varchar Data" in Books
Online.

SELECT @strsql = 'SELECT * FROM tblTest WHERE Field1 Like ''blah'

Also, avoid using double quotes around strings - they're treated as
identifier delimiters when SET QUOTED_IDENTIFIER is ON, and since you
need that option ON to use indexed views and indexed computed columns,
it's best to stick to single quotes in all cases. See "Delimited
Identifiers" in Books Online for more details.

Simon

Use the below code with  "set quoted_identifier off"


set quoted_identifier off
declare @strsql nvarchar(500)
SELECT @strsql = "SELECT * FROM tblTest WHERE Field1 Like 'blah%'"

Thanks
Hari
SQL Server MVP

"Steve" <steve@hello.com> wrote in message news:432ff8b5_3@x-privat.org...
> How do I get a single quote (') in a NVARCHAR string in MS SQL Server?
>
> e.g. SELECT @strsql = "SELECT * FROM tblTest WHERE Field1 Like 'blah''
>
> Obviously this is invalid as the single quote before "blah" would end the
> varchar string.
>
> How do I get round this?
>

Use single-quotes to delimit the string.  Use double single-quotes inside
the string:

SELECT @strsql = ''SELECT * FROM tblTest WHERE Field1 Like ''blah'''


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON   Canada
www.pinpub.com
.
"Steve" <steve@hello.com> wrote in message news:432ff8b5_3@x-privat.org...
How do I get a single quote (') in a NVARCHAR string in MS SQL Server?

e.g. SELECT @strsql = "SELECT * FROM tblTest WHERE Field1 Like 'blah''

Obviously this is invalid as the single quote before "blah" would end the
varchar string.

How do I get round this?

Typo:

SELECT @strsql = 'SELECT * FROM tblTest WHERE Field1 Like ''blah'''


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON   Canada
www.pinpub.com
.
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:eZ0HNxdvFHA.3000@TK2MSFTNGP12.phx.gbl...
Use single-quotes to delimit the string.  Use double single-quotes inside
the string:

SELECT @strsql = ''SELECT * FROM tblTest WHERE Field1 Like ''blah'''


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON   Canada
www.pinpub.com
.
"Steve" <steve@hello.com> wrote in message news:432ff8b5_3@x-privat.org...
How do I get a single quote (') in a NVARCHAR string in MS SQL Server?

e.g. SELECT @strsql = "SELECT * FROM tblTest WHERE Field1 Like 'blah''

Obviously this is invalid as the single quote before "blah" would end the
varchar string.

How do I get round this?

> Use the below code with  "set quoted_identifier off"

This would not be my recommendation.  This is not a typical setting and is
likely to cause confusion for other users of the system...

Xref: number1.nntp.dca.giganews.com comp.databases.ms-sqlserver:158204 micro
soft.public.sqlserver.programming:593281 microsoft.public.sqlserver.server:4
17247

> e.g. SELECT @strsql = "SELECT * FROM tblTest WHERE Field1 Like 'blah''

Why is this in a variable?  Why don't you just execute

SELECT * FROM tblTest WHERE Column1 = 'blah'
or
SELECT * FROM tblTest WHERE Column1 LIKE 'blah%'

?

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in mess
age
news:uneus9dvFHA.612@TK2MSFTNGP10.phx.gbl... 
>
> Why is this in a variable?  Why don't you just execute
>
> SELECT * FROM tblTest WHERE Column1 = 'blah'
> or
> SELECT * FROM tblTest WHERE Column1 LIKE 'blah%'
>
> ?

That isn't my actual code and was just an example.

Thanks to all who have responded.