473,704 Members | 2,990 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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?
Sep 20 '05 #1
8 23579
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
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_IDENTIFI ER 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
Use the below code with "set quoted_identifi er off"
set quoted_identifi er off
declare @strsql nvarchar(500)
SELECT @strsql = "SELECT * FROM tblTest WHERE Field1 Like 'blah%'"

Thanks
Hari
SQL Server MVP

"Steve" <st***@hello.co m> 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
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.co m> 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
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******** ******@TK2MSFTN GP12.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.co m> 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
> Use the below code with "set quoted_identifi er 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
> 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
"Aaron Bertrand [SQL Server MVP]" <te*****@dnartr eb.noraa> wrote in message
news:un******** *****@TK2MSFTNG P10.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
21485
by: Hugo Coolens | last post by:
I need to use the str_replace function for a php-script which works together with html/javascript. In the script I have to perform things like: $arabic=str_replace ("'","\'", $row); $arabic=str_replace ("sh", "\š", $row); the second line works perfectly, however I can't make the first one work, it should replace a single quote with a quote preceded by a backslash. I presume I have to escape some characters to make this happen however...
4
10632
by: sankofa | last post by:
hi, i can't seem to be able to escape my single quote properly... is it even possible in javascript? this is a portion of my code.. var DLEWIS="Pastor Lewis"; .... Sermon is a yser-defined class .. var en_20031102=new Sermon("11/02",DLEWIS,"The Lord\'s Supper: The Art Of
6
22075
by: DDE | last post by:
Hi, wich way can I escape a string sent to an OleDbComman containing single quote? Thanks Dominique
3
11240
by: micmic | last post by:
Deear all experts, In the MySQL, we can use escape character '\' to save the STRING WITH single quote into database (eg. we would like to insert into table "tbl_ABC"with the string ab'c, we can use the following SQL query to insert into database : Insert into tbl_ABC VALUES ('ab\'c'); ). Also there is some functions in mySQL api so that we can save the single quote into MySql database using C programme. But it seems that i cannot do...
4
1639
by: Paul | last post by:
I've got some code that adds a single quote to any ad hoc queries that appear to look like hacks. For instance, if somebody enters ' OR 1=1 -- then this code adds another single quote the string to neutralize it. The neutralized string becomes '' OR 1=1 --. The problem is that when I try to concatenate this string into a SQL insert statement, the extra single quote is lost. It diskappears! The hack can then get to the DB. I tried...
3
6386
by: Mitch | last post by:
I have been trying to use TrimEnd() to eliminate a single quote from the end of a string. I have used "'", ''', and a few others without success. Does anyone have experience with TrimEnd()?
5
7487
by: Mark Woodward | last post by:
Hi all, I'm trying to validate text in a HTML input field. How do I *allow* a single quote? // catch any nasty characters (eg !@#$%^&*()/\) $match = '/^+$/'; $valid_srch = preg_match($match, $res_description); if (!$valid_srch) { ...
4
6242
by: fniles | last post by:
I am looping thru DataReader and constructing a sql query to insert to another database. When the data type of the field is string I insert the field value using a single quote. When the value of the field has a single quote in it like "O'Toole", how can I construct the string ? Do While drSQL.Read sSQL = "insert into " & sTableName & " values (" sSQL2 = ""
5
26936
karenRoss
by: karenRoss | last post by:
Am I even close to right? I dont recall how many single quotes it takes to represent a single quote in a aspx.cs code behind page. if (firstname.Contains( " ' " )) { indexof = firstname.IndexOf(" ' "); firstname.Insert(indexof + 1, " ' "); } if (lastname.Contains("''")) { indexof = lastname.IndexOf(" ' ");
0
8767
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8687
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8977
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7891
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6605
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5931
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4439
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3135
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2080
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.