Jr. Developer asked me other day how to escape single quote?
User can escape single quote using two single quotes (NOT double quote).
Following T-SQL Script will give error
USE AdventureWorks
GO
SELECT *
FROM Person.Address
WHERE City = ‘Villeneuve-d’Ascq’
GO
Resultset:
Error: 105 Unclosed quotation mark after the character string ‘
To fix the error mentioned above, escape single quotes with two single quotes as displayed in following example.
USE AdventureWorks
GO
SELECT *
FROM Person.Address
WHERE City = 'Villeneuve-d''Ascq'
GO
Reference : Pinal Dave (http://blog.SQLAuthority.com)
@ENTITY_NAME NVARCHAR(100)
IF @ENTITY_NAME = ‘%’
BEGIN
SELECT @WHERE = @WHERE + ‘ AND (T.ENTITY_ID LIKE ”’ + @ENTITY_NAME + ”’ OR T.ENTITY_ID IS NULL)’
END
(If @ENTITY_NAME value is ‘ramarao’ in this I did not get any problem. But, ‘ramarao’s ‘ I am getting error like Unclosed quotation mark after the character string ‘). Can you please help out me by giving proper code (how to replace two single quotes in place of one single quote).
Thanks in Advance.
Thanks,
B. Rama Rao
Problem is mother of solution…
Here’s one of the solution to pass single quote (‘) a.k.a. Apostrophe in searches as variables.
Just add the following to your T-SQL
Declare @find nvarchar(5)
Declare @replace nvarchar(5)
Declare @yourvariable –this is the variable used in search
Set @find = ”+ char(39) +” — these are two single quotes
Set @replace = ”+char(39)+char(39)+”
Set @yourvariable = replace(@yourvariable,@find,@replace) –this will replace one single quote with two double quote
This will be helpful in statement such as select * from tbl_name where column_name like ‘%+@yourvariable+%’
Hope this helps and saves some development time.
bhattji
Thank you Nikhil
Thank you for this solution!
write a simple example for me, which meets my requirement.
Thankx
OK this is easiest example
My value that I want to query is -> Ma’ry (you will see it has ‘ among word)
select Name
from employee
where Name = ‘Ma”ry’
word by word -> Single quote+Ma+Single quote+Single quote+ry+Single quote
This is a helpful post.
This is hideous functionality. Completely unintuitive. One more reason for me to hate SQL Server…
How to find central in below string in sql
AWA/Central/_catalogs/masterpage
Thanks in advance
Chris, this is standard ANSI SQL behavior, you [removed word].
Oh, and for you other [removed word]:
set quoted_identifier off
select “Hello ‘you’ if that’s your real name”
set quoted identifier on
Learn how to learn!
@Martin
Please do not use abusive language. It is not helpful to anyone.
Thank you Brian Tkatch
For pointing this out. I have edited abusive response.
Kind Regards,
Pinal
Hello, I have a set of columns I need use in a query but they contain single quote elements.
e.g.: [Mark's House]
Whenever I try to do this:
SET @sql_query = ‘INSERT INTO ‘ + @TABLE_XYZ + ‘ ( ‘ + @TRG_FIELDS + ‘ ) VALUES ( ‘ + @COLUMN_ABC ‘ + ‘)’ ;
EXEC ( @sql_query );
The query works as long as the columns do not contain the single quote element.
Can anyone give me hand on this?
I am no longer sure of what could be the solution to this.
I am sure it’s simple. :(
@ed.
Either escape the single-quote with another, or encase it in brackets.
VALUES ([ ' + @COLUMN_ABC + '])’’;
Thanks Brian. I am lost with the double quote at the end of the line. This is the query as I have it:
SET @sql_query = ‘INSERT INTO ‘ + @TRG__db_table + ‘ ( ‘ + @TRG__tb_fields + ‘ ) VALUES ( ‘ + @user_name + ‘, ‘ + @TRG__address + ‘, ‘ + @TRG__user_name + ‘ , ‘ + @TRG__city_town + ‘, ‘ + @TRG__state_province + ‘ )’;
PRINT ‘SQL-Query: ‘ + @sql_query;
Should I make it like:
SET @sql_query = ‘INSERT INTO [' + @TRG__db_table + '] ( [' + @TRG__tb_fields + '] ) VALUES ( [' + @user_name + '], [' + @TRG__address + '], [' + @TRG__user_name + '], [' + @TRG__city_town + '], [' + @TRG__state_province + '] )’;
If this is ok, where does the double quote should be placed?
I have tried your suggestion and this is the result:
SET @sql_query = “SELECT COUNT( loc_name + ctv_name + xyz_code ) FROM [dbo].[' + @country + '_Listings] WHERE ( loc_name = [' + @province + '] ) AND ( ctv_name = [' + @city + '] ) AND ( sic_code = [' + @xyz_code + '] )”;
Msg 103, Level 15, State 4, Procedure set_RECORDS, Line 54
The identifier that starts with ‘
SELECT COUNT( loc_name + ctv_name + xyz_code ) FROM [dbo].[' + @country + '_Listings]‘
is too long. Maximum length is 128.
@ed.
Sorry about that. The double-quote should be a single-quote. It needs to end off the ])
In your last example too, make the double-quote a single-quote.
Double-quote are used to quote names, such as a TABLE or COLUMN. Single-quotes are used for values.
@ed.
I made a mistake. Brackets are for objects, not literals.
So, escape it with a single-quote. REPLACE(col, ””, ”””)
SET @sql_query = ‘SELECT COUNT(loc_name + ctv_name + xyz_code )’
+ ‘ FROM [dbo].[' + @country + '_Listings]‘
+ ‘ WHERE loc_name = ‘ + REPLACE(@province, ””, ”””)
+ ‘ AND ctv_name = ‘ + REPLACE(@city, ””, ”””)
+ ‘ AND sic_code = ‘ + REPLACE(@xyz_code, ””, ”””);
The confusion here is that you are using dynamic SQL. If you use parameters instead, all these problems go away.
Obviously, you want dynamic SQL because of the TABLE which is dynamic. But is that really what you want? Why not have one Listing TABLE with country as a COLUMN inside it?
We have a DataSet with Table Adapters built for MSAccess using OLEDB. We are using Visual Studio 2005 and .Net 2.0.
When the designer creates insert, update and delete queries it encloses the table name and field names with a ` (ASCII char 96).
For example:
INSERT INTO `table` (`col1`, `col2`, `col3`) values (….)
Is there a reason for this character? Assuming it’s for escape purposes, is there a setting to make Visual Studio use another character, eg “[" or "]“?
Our main goal is to use the same SQL queries (basic inserts, updates, deletes) in both MS Access and SQL Server.
Our app was originally written for Access using the VS designer to generate the queries.
After testing these queries, we found that if the ` char was removed and replaced by [ ] brackets, then all the queries would work in both db’s.
I know this is an old post, but I’d like to thank you because that’s just what I needed
Following dbcc statement work fine from QA, but it failing using in a sql job.
Please help…..
Use “EPO_USLAX-EPO01″
go
DBCC SHRINKFILE (“ePO_USLAX-EPO01″, 20)
GO
DBCC SHRINKFILE (“ePO_USLAX-EPO01_log”, 10)
go
@rana
Two Options.
1. Create a procedure and execute that procedure in the job.
USE [EPO_USLAX-EPO01]
Create Proc [USp_ShrinkDB]
AS
DBCC SHRINKFILE ( ‘ePO_USLAX-EPO01′, 20)
DBCC SHRINKFILE (‘ePO_USLAX-EPO01_log’, 10)
go
put this script in the job.
Exec [EPO_USLAX-EPO01]..[USp_ShrinkDB]
2. What if you do this in two different steps of the same job, Something like this,
Create a job,
First Step in that job is to only shrink one file
USE [EPO_USLAX-EPO01]
DBCC SHRINKFILE ( ‘ePO_USLAX-EPO01′, 20)
Second step in that job will be to shrink second file.
USE [EPO_USLAX-EPO01]
DBCC SHRINKFILE (‘ePO_USLAX-EPO01_log’, 10)
~IM.
Very good advise Imran.
Edited for some typo
@tableName varchar(100),
@Code nvarchar(10),
@label nvarchar(max),
@Lang nchar(2),
@Reg nchar(2),
@Fran nchar(3)
SET @query = ‘INSERT INTO ‘ + @tableName
+ ‘ (Code,label,Lang,Reg,Fran) VALUES’
+’ (”’+ @Code +”’,”’ @label +”’,”’+@Lang+”’,”’+@Reg+”’,”’+@Fran+”’)’
execute(@query)
this query will not work when i have label like this
A00 ”B & Y – “NIPO””
how to fix this
@sqlquestions
Did you try escaping all those quotes?
Just go with brian advice, you can either replace or remove quotes and it will work. If any one have any thing better please put comment.
–remove single quote
set @label = REPLACE(@label ,””,”)
–replace single quote with doule quote
set @label = REPLACE(@label, ””, ‘”‘);
Thanks
Thanks to God, the one and only, that I found this post. Thank you very much for this post.
I need to get the error resolved for the code below
————–error————————–
USE AVME
Declare @SQL nvarchar(MAX)
Declare @docpath nvarchar(MAX)
Set @docpath = N’E:\Workflow\SPP\Attachments\ModelCodes\claim.xls’
Set @SQL = ‘INSERT INTO [Tactical.Claim_LineItems_BULK_Audi] ([JObCreator], [ModelCode],
[ChassisNo], [CommissionNo], [AGSupport],[AVMESupport], [Brand], [Remark])
SELECT *
FROM OPENROWSET (”Microsoft.ACE.OLEDB.12.0”, ”Excel 12.0;;Database=”’+@docpath+”’,
”select * from [Sheet1$]”)’
PRINT @SQL
EXEC(@SQL)
————error———————————————-
INSERT INTO [Tactical.Claim_LineItems_BULK_Audi] ([JObCreator], [ModelCode],
[ChassisNo], [CommissionNo], [AGSupport],[AVMESupport], [Brand], [Remark])
SELECT *
FROM OPENROWSET (‘Microsoft.ACE.OLEDB.12.0′, ‘Excel 12.0;;Database=’E:\Workflow\SPP\Attachments\ModelCodes\claim.xls’,
‘select * from [Sheet1$]‘)
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ‘E’.
Msg 105, Level 15, State 1, Line 5
Unclosed quotation mark after the character string ‘)’.
Dont use single quote around file name
Hi,
I have a new variation on this problem. I have a varchar() field called Notes where users can enter anything they want. If they use a single quote for a grammatical contraction, then it causes an error when I try to pass the string in an UPDATE statement. So, for intance:
New account, didn’t ask for conversion.
causes an “Unclosed quotation mark after the character string” error.
I noticed that when I BULK imported the table into SQLServer, it wrapped all these text strings in double quotes:
“New account, didn’t ask for conversion.”
but that doesn’t seem to fix the problem. When I try to pass that text string in an UPDATE statement I still get the error.
Is there a way to wrap up these text strings so they automatically get escaped by SQLServer? I understand the syntax for escaping them when I’m writing the whole SQL statement, but I can’t ask my users to avoid single quotes in their comments.
Thanks,
Bill J.
You need to double the single quotes
http://beyondrelational.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx
Hi Bill,
I had similar problem like yours. Just repeating code from above
Just add the following to your T-SQL
Declare @find nvarchar(5)
Declare @replace nvarchar(5)
Declare @notes –this is the variable used in your update statement
Set @find = ”+ char(39) +” — these are two single quotes
Set @replace = ”+char(39)+char(39)+”
Set @notes = replace(@notes,@find,@replace) –this will replace one single quote with two double quote.
This will be helpful in statement such as Update tbl_name set = ‘%+@notes+%’
where
I hope this helps.
Cheers, bhattji
Also refer
http://beyondrelational.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx
Hi Pinal,
My question is whether sql server 2008 has taken care of apostrophe problem. I am not facing any problem of apostrophe in my code when i insert data in table from UI. I am using SQL server 2008 and stored procedure to insert data from UI to database. I tried to find but not able to find any blog where it is mentioned so still in doubt how SP is adding data with apostrophe in table. I want to make myself double sure.
This does not always work…Here is an example:
drop table dirresults
CREATE TABLE DirResults (Diroutput VARCHAR(500))
INSERT DirResults
exec master..xp_cmdshell ‘osql -S127.0.0.1,1450 -T -Q “exec sp_executesql N”select * from pubs..authors where state = ”a”””‘
Hi Gurus,
I hope here I am going to get my answer.
The code is working from SQL Server 2000 to 2008.
However the application is crash in 2008R2.
Here is the code:
CREATE TABLE abc (
col1 varchar (7000) NOT NULL,
PRIMARY KEY (col1))
INSERT INTO abc ( col1 )
VALUES( ‘a’ ) // Different Values in a loop
Check the SQLDB CODE If It is Duplicate Then
Do Some Calculation
ELSE
Loop
In 2008R2 as soon as it finds a duplicate it crashed.
Thanks for really good post.Best regards.
I am using a fully loaded sqlite3 database where embedded single and double quotes are included in many of the varchar fields. What select query could i write to simply query the database without things breaking?
for example
where col2 is know to contain embedded single and double quotes
Select col1, col2 from table_Name;
ALTER PROCEDURE [dbo].[DBSP_SF_GetAccountsLikeAccNo]
@AccountNo VARCHAR(20),
@BusinessName VARCHAR(40),
@Type VARCHAR(2)
AS
BEGIN
DECLARE @str VARCHAR(100)
SELECT @str=REPLACE(@BusinessName,””,”)
IF @Type = ‘AN’
BEGIN
SELECT AccountNo, BusinessName FROM CustomerDetails WITH (NOLOCK) WHERE AccountNo like @AccountNo + ‘%’
END
ELSE IF @Type = ‘BN’
BEGIN
SELECT AccountNo, BusinessName FROM CustomerDetails WITH (NOLOCK) WHERE BusinessName like @str + ‘%’
END
END
if the value is Andersons The Grocery Store .in this I did not get any problem. But, ‘Anderson’s “The Grocery Store”‘ I am getting error like Unclosed quotation mark after the character string ‘). Can you please help out me by giving proper code (how to replace two single quotes in place of one single quote).
plz helpme any modify my SP
Thank you so much Nikhil Bhatt
thanks! Big fan of your posts
Hi Pinal,
I am developing a .net application and as part of this, i have a stored procedure that gets me the data with required format. This stored procedure has dynamic sql in it where the formatting of the data takes place.
Below is the query that i am using to format any data based on its column datatype. This query just does the following, I have used master DB for this example,
1. @query variable is formed with a sql query where this query has a case statement in which column name is passed to scalar function(udfSelDataTypeOfColumn) and datatype is got from it.
2. If this datatype is of CHAR or NCHAR or NVARCHAR or VARCHAR then i want the output of the column value enclosed in a single quote.
Suppose, the output value is a varchar with value as Pinal then this query has to return ‘Pinal’ else for other datatypes same value can be returned like (int)1234, (bit)1
I tried formatting with single quotes and concatinating but no luck.
Please let me know a solution to this problem and explain me how single quotes intepretation is done by sql runtime.
USE master
DECLARE @query NVARCHAR(MAX),
@charDataType NVARCHAR(20)
SELECT @charDataType=”’%char%”’
select @query=’SELECT TOP 10
CASE WHEN dbo.udfSelDataTypeOfColumn(”name”) like ‘+@charDataType+ ‘then ”” +name+ ”” else ”name” end
FROM spt_values WITH(NOLOCK)’
EXEC sp_executesql @query
I have getting same problem in SQL string kindly help me out about this error my sring is
“INSERT INTO LoginMaster(UserName, Password, DistrictID, AgencyID, Address, BlockID, PanchayatID, UserType, ContactNo, EmailAddress, SQuestion, SAnswer, Status) VALUES (‘” + UserName.Text + “‘, ‘” & Password.Text & “‘,’” & district.SelectedValue & “‘,’” & agency.SelectedValue & “‘,’” & AgencyAddress.Text & “‘,’0′,’0′,’” & UserType.SelectedValue & “‘,’” & contactno.Text & “‘,’” & emailaddress.Text & “‘ ,’” & SQuestion.Text & “‘,’” & SAnswer.Text & “‘,’1′)”
plz help me
Thanks.