Single quote and double quote issue in sql server

Question

i am sending xml from my .net application to sql server

my line item values looks like  Total Liabilities & Shareholders' Equity  where single quote exist.

so i replace that value with double quote before insert that data in table. this way i replace single quote with double quote

    INSERT INTO TblLineItemTemplate(Ticker,LineItem,XFundCode,Action,UserID) 
VALUES (TRIM(@TickerID),REPLACE(TRIM(@LineItem),'''',''''''),TRIM(@XFundCode),'I',@UserID)                  

when i am searching that value with single quote or double quote then i am not getting that value from table.

please tell me a fix. thanks

Answer 1

when i am searching that value with single quote or double quote then i am not getting that value from table.

please tell me a fix. thanks

Your code is replacing the single quote in the @LineItem value with 2 single quotes, not a double quote character. So value Total Liabilities & Shareholders' Equity becomes Total Liabilities & Shareholders'' Equity.

It's not clear to me why you are replacing the quote. If the application is providing an XML fragment as the @LineItem value and you're objective is to store the value in the database without XML encoding, you can cast the value as XML to extract the actual text value:

INSERT INTO TblLineItemTemplate(Ticker,LineItem,XFundCode,Action,UserID) 
	VALUES (TRIM(@TickerID),TRIM(CAST(@LineItem AS xml).value('.','varchar(500)')),TRIM(@XFundCode),'I',@UserID);

The value stored in the column will then be Total Liabilities & Shareholders' Equity, with XML entity references replaced with the proper characters.

---

Dan Guzman, Data Platform MVP, http://www.dbdelta.com

Answer 2

Sir basically i am sending a huge xml from .net winform application to sql server store procedure. in line item some time there is single quote. i am loading xml into cursor in store proc and insert data one by one. when there is single quote in line item then i am getting error for insertion from SP

 i am pasting here one records from xml

<TickerSectionLI>
    <Ticker>TER</Ticker>
    <Section>Key Financials</Section>
    <LI>Total Liabilities &amp; Shareholders' Equity</LI>
    <StandrdFormula />
    <StandrdFormulaActual />
    <AllowedDecimalPlace>1</AllowedDecimalPlace>
    <CurrencySign>$</CurrencySign>
    <CurrencyCode>en-US</CurrencyCode>
    <AllowPercentageSign>false</AllowPercentageSign>
    <AllowComma>true</AllowComma>
    <QCCheck>false</QCCheck>
    <QCType>4Q=FY</QCType>
    <BlueMatrix1stElement>As % of Total Liabilities and Shareholders' Equity</BlueMatrix1stElement>
    <BlueMatrix1stElementFormula>"Key Financials~Total Liabilities~2278"/"Key Financials~Total Liabilities &amp; Shareholders' Equity~9261"</BlueMatrix1stElementFormula>
    <DevelopmentStage />
    <CrossCalc1Q />
    <CrossCalc2Q />
    <CrossCalc3Q />
    <CrossCalc4Q />
    <CrossCalcFY />
    <GH_FontStyle>Regular</GH_FontStyle>
    <GH_Strikeout>false</GH_Strikeout>
    <FontStyle>Regular</FontStyle>
    <Strikeout>false</Strikeout>
  </TickerSectionLI>

see this one sir where single quote exist in LI 

<LI>Total Liabilities &amp; Shareholders' Equity</LI>

sql server giving error. so when i am iterating data in cursor then i replace single quote this way

    INSERT INTO TblLineItemTemplate(TickerID,LineItem,XFundCode,Action,UserID) VALUES (TRIM(@TickerID),REPLACE(TRIM(@LineItem),'''',''''''),TRIM(@XFundCode),'I',@UserID)                    

now i am not getting error and when i see how data is store in table there i saw two single quote store in LI's name.

but when i searching the same data with single quote then i am not getting that data....that is my issue. if any data has two quote in its value then how to search that data ?

thanks

Answer 3

Single quotes are escaped by doubling them up, just as you've shown us in your example. The following SQL illustrates this functionality. I tested it on SQL Server 2008:

DECLARE @my_table TABLE (
    [value] VARCHAR(200)
)

INSERT INTO @my_table VALUES ('hi, my name''s tim.')

SELECT * FROM @my_table
Results
value
==================
hi, my name's tim.

Answer 4

Sir basically i am sending a huge xml from .net winform application to sql server store procedure. in line item some time there is single quote. i am loading xml into cursor in store proc and insert data one by one. when there is single quote in line item then i am getting error for insertion from SP

<LI>Total Liabilities &amp; Shareholders' Equity</LI>

sql server giving error. so when i am iterating data in cursor then i replace single quote this way

    INSERT INTO TblLineItemTemplate(TickerID,LineItem,XFundCode,Action,UserID) VALUES (TRIM(@TickerID),REPLACE(TRIM(@LineItem),'''',''''''),TRIM(@XFundCode),'I',@UserID)           

You might not need a cursor for this task. One can use INSERT...SELECT to insert many rows at once from XML. The source SELECT statement can use XML data type methods to return the individual rows and column values from the XML parameter. 

I don't know what error you are getting and you haven't included the problem code in the cursor loop that causes the error. Single quotes in values will be problematic only if you are using dynamic SQL and are not properly using a parameterized query.

Below is an example you can adapt for you actual XML and extend with your transformations.

DECLARE @xml xml = N'
<Tickers>
<TickerSectionLI>
    <Ticker>TER</Ticker>
    <Section>Key Financials</Section>
    <LI>Total Liabilities &amp; Shareholders'' Equity</LI>
  </TickerSectionLI>
<TickerSectionLI>
    <Ticker>TER2</Ticker>
    <Section>Key Financials2</Section>
    <LI>Total Liabilities &amp; Shareholders2'' Equity</LI>
  </TickerSectionLI>
</Tickers>
'
--INSERT INTO TblLineItemTemplate(Ticker,LineItem) 
SELECT 
	 TickerSectionLI.value('Ticker[1]','varchar(500)') AS Ticker
	,TickerSectionLI.value('LI[1]','varchar(500)') AS LineItem
FROM @xml.nodes('/Tickers/TickerSectionLI') AS Tickers(TickerSectionLI);
GO

---

Dan Guzman, Data Platform MVP, http://www.dbdelta.com

Answer 5

just sharing small my code snippet

DECLARE CURRECORD                            
  CURSOR LOCAL FOR                            
  SELECT Section,LineItem,StandardDate,StandardValue,XFundCode                            
  FROM OPENXML (@XMLFORMAT, '/Root/PeriodicalData', 2)                            
  WITH                   
  (                            
   Section   VARCHAR(100),                            
   LineItem  VARCHAR(100),                            
   StandardDate VARCHAR(20),                            
   StandardValue VARCHAR(20),                            
   XFundCode  VARCHAR(20)                            
  )               
              
  -- open cursor                            
  OPEN CURRECORD                            
  FETCH NEXT FROM CURRECORD INTO @Section,@LineItem,@StandardDate,@StandardValue,@XFundCode                          
                            
  -- iterate in cursor to fetch value                            
  WHILE (@@FETCH_STATUS=0)                            
  BEGIN                            
  
  --REPLACE(TRIM(@LineItem),'''','''''')  
  --char(39)  
   IF NOT EXISTS(SELECT * FROM TblLineItemTemplate WHERE TRIM(TickerID)=TRIM(@TickerID) AND TRIM(LineItem)=REPLACE(TRIM(@LineItem),'''',''''''))         
   BEGIN                    
    INSERT INTO TblLineItemTemplate(TickerID,LineItem,XFundCode,Action,UserID) VALUES (TRIM(@TickerID),REPLACE(TRIM(@LineItem),'''',''''''),TRIM(@XFundCode),'I',@UserID)                    
    SET @LineItemID = SCOPE_IDENTITY()                       
   END                    
   ELSE                    
   BEGIN                    
    SELECT @LineItemID=ID FROM TblLineItemTemplate WHERE TRIM(TickerID)=TRIM(@TickerID) AND TRIM(LineItem)=TRIM(@LineItem)                    
   END 
END

i load the xml into cursor and iterate in cursor to insert row one by one

see how i convert single quote with two single code in code before insert

INSERT INTO TblLineItemTemplate(TickerID,LineItem,XFundCode,Action,UserID) 
VALUES (TRIM(@TickerID),REPLACE(TRIM(@LineItem),'''',''''''),TRIM(@XFundCode),'I',@UserID)                    

when i check data in table after insert i saw data store in table like this way which is not right

Total Liabilities & Shareholders'''' Equity

this way i replace single quote with 2 single quote REPLACE(TRIM(@LineItem),'''','''''')   

why replace function insert 4 single quote in data ?

where i am making the mistake ?

Answer 6

please see my last post and answer if possible. thanks

Answer 7

What exactly is the error message you are getting when doing the insert?  Also what version of SQL Server are you using? Please post the results of SELECT @@VERSION.  What is the datatype of TblLineItemTemplate.LineItem?

There is no reason for the insert to fail if the variable has a single quote value, unless you are actually using "dynamic SQL" to expand the variable and run the insert command.

Answer 8

MS chose not to properly escape quotes in XML.  https://feedback.azure.com/forums/908035-sql-server/suggestions/32895817-sql-server-for-xml-doesn-t-escape-single-quote-in

For proper XML, what you actually want is:

REPLACE(REPLACE(TRIM(@LineItem),'''','''),'"','"')

Answer 9

just sharing small my code snippet

DECLARE CURRECORD                            
  CURSOR LOCAL FOR                            
  SELECT Section,LineItem,StandardDate,StandardValue,XFundCode                            
  FROM OPENXML (@XMLFORMAT, '/Root/PeriodicalData', 2)                            
  WITH                   
  (                            
   Section   VARCHAR(100),                            
   LineItem  VARCHAR(100),                            
   StandardDate VARCHAR(20),                            
   StandardValue VARCHAR(20),                            
   XFundCode  VARCHAR(20)                            
  )               
              
  -- open cursor                            
  OPEN CURRECORD                            
  FETCH NEXT FROM CURRECORD INTO @Section,@LineItem,@StandardDate,@StandardValue,@XFundCode                          
                            
  -- iterate in cursor to fetch value                            
  WHILE (@@FETCH_STATUS=0)                            
  BEGIN                            
  
  --REPLACE(TRIM(@LineItem),'''','''''')  
  --char(39)  
   IF NOT EXISTS(SELECT * FROM TblLineItemTemplate WHERE TRIM(TickerID)=TRIM(@TickerID) AND TRIM(LineItem)=REPLACE(TRIM(@LineItem),'''',''''''))         
   BEGIN                    
    INSERT INTO TblLineItemTemplate(TickerID,LineItem,XFundCode,Action,UserID) VALUES (TRIM(@TickerID),REPLACE(TRIM(@LineItem),'''',''''''),TRIM(@XFundCode),'I',@UserID)                    
    SET @LineItemID = SCOPE_IDENTITY()                       
   END                    
   ELSE                    
   BEGIN                    
    SELECT @LineItemID=ID FROM TblLineItemTemplate WHERE TRIM(TickerID)=TRIM(@TickerID) AND TRIM(LineItem)=TRIM(@LineItem)                    
   END 
END

i load the xml into cursor and iterate in cursor to insert row one by one

see how i convert single quote with two single code in code before insert

INSERT INTO TblLineItemTemplate(TickerID,LineItem,XFundCode,Action,UserID) 
VALUES (TRIM(@TickerID),REPLACE(TRIM(@LineItem),'''',''''''),TRIM(@XFundCode),'I',@UserID)                    

when i check data in table after insert i saw data store in table like this way which is not right

Total Liabilities & Shareholders'''' Equity

this way i replace single quote with 2 single quote REPLACE(TRIM(@LineItem),'''','''''')   

why replace function insert 4 single quote in data ?

where i am making the mistake ?

What happens if you omit the REPLACE entirely? It will help us to identify the root cause of the problem if you provide code that actually shows the error you were getting. The code you've posted so far will not cause such an error and I suspect the problems you are having with REPLACE is only a workaround for a different flaw in the code.

 

---

Dan Guzman, Data Platform MVP, http://www.dbdelta.com

Answer 10

sql server version

Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)   Aug 22 2017 17:04:49   
Copyright (C) 2017 Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) 

i am not getting any error

i load xml data into cursor and iterate in data one by one and insert each data into table. some time there is single quote in data. so i replace single quote like this way 

INSERT INTO TblLineItemTemplate(TickerID,LineItem,XFundCode,Action,UserID) 
VALUES (TRIM(@TickerID),REPLACE(TRIM(@LineItem),'''',''''''),TRIM(@XFundCode),'I',@UserID)  

but after insertion done when i am checking data in table then i saw there are four single quote which is wrong.

Total Liabilities & Shareholders'''' Equity

so when i query my data like this way select * from tbl1 where Li='Total Liabilities & Shareholders' Equity'

then i am not getting my data because there are four single code.

so my i do not understand why sql server replace function is not working as expected. see my replace usage please and tell me good work around. thanks

Answer 11

now i replace one single quote with two single quote in my xml data from c# front end but that xml data goes to SP

but when SP insert that data into table then i saw two single quote data saved in table which is wrong.

how to over come this problem?

thanks

Answer 12

Please see my response on how to properly handle quotes in XML data.

Answer 13

at last i solve this single quote issue this way

in my SP i use

SET QUOTED_IDENTIFIER OFF; 

here i insert data with single quote and it worked with adding another single quote as escape character.

SET QUOTED_IDENTIFIER ON;  

thanks

Answer 14

Sir,

at last i solved this single quote issue this way

in my SP i use

SET QUOTED_IDENTIFIER OFF; 

here i insert data with single quote and it worked with adding another single quote as escape character.

SET QUOTED_IDENTIFIER ON;  

thanks

Answer 15

SET QUOTED_IDENTIFIER OFF;

here i insert data with single quote and it worked with adding another single quote as escape character.

SET QUOTED_IDENTIFIER ON; 

I (like everyone else) was never able to understand why you were messing around those quotes anyway, but I have some bad news for you: QUOTED_IDENTIFIER is not a solution. This is a legacy option, and there are several features that only work if QUOTED_IDENTIFIER is ON. One such feature is using the XML type methods.

Most likely the solution is not to mess with the quotes at all.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 16

Sir please discuss what bad effect may arise after using QUOTED_IDENTIFIER used ?

give me few example and scenarios. thanks

Answer 17

Lets start over.  

There is no reason for you to replace the single or double quotes in the XML string.  If you are having problems searching for strings, your problem is in your search string, not the XML source.

What exactly is the problem you are trying to solve, with examples.

Answer 18

Sir please discuss what bad effect may arise after using QUOTED_IDENTIFIER used ?

XML type methods! Which you need to manipulate your XML!

And that is just the start. Filtered indexes, indexed views etc. You will sooner or later get and error if you run with QUOTED_IDENTIFIER OFF. You have been warned.

And as Tom says, there is no reason for this replace business at all. People have showed you how to properly to shred XML in table format. Did you ever try it?

---

Erland Sommarskog, SQL Server MVP, [e-mail]