By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,940 Members | 1,391 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,940 IT Pros & Developers. It's quick & easy.

Single quote in NVARCHAR string?

P: n/a
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?
Sep 20 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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.

Sep 20 '05 #2

P: n/a
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

Sep 20 '05 #3

P: n/a
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" <st***@hello.com> wrote in message news:43********@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?

Sep 20 '05 #4

P: n/a
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" <st***@hello.com> wrote in message news:43********@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?
Sep 20 '05 #5

P: n/a
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" <to*@dont.spam.me.cips.ca> wrote in message
news:eZ**************@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" <st***@hello.com> wrote in message news:43********@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?
Sep 20 '05 #6

P: n/a
> 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...
Sep 20 '05 #7

P: n/a
> 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%'

?
Sep 20 '05 #8

P: n/a
"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraa> wrote in message
news:un*************@TK2MSFTNGP10.phx.gbl...
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%'

?


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

Thanks to all who have responded.
Sep 20 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.