SQL Server Replace single quote with double quote

Sudip Bhatt 2,271 Reputation points
2020-11-28T19:37:17.717+00:00

I am extracting data from xml and there could be single quote in data. so when i will insert then i want to convert single quote in data to double quote. i tried this way.....please tell me does it work properly?

            INSERT INTO #TmpTenQKData 
                SELECT REPLACE(col.value('(Section/text())[1]', 'NVARCHAR(MAX)'),'''','''''') AS Section
                    ,REPLACE(col.value('(LineItem/text())[1]', 'NVARCHAR(MAX)'),'''','''''')  AS LineItem   
                    ,REPLACE(col.value('(XFundCode/text())[1]', 'NVARCHAR(MAX)'),'''','''''') AS XFundCode
                    ,col.value('(StandardDate/text())[1]', 'NVARCHAR(MAX)') AS StandardDate
                    ,col.value('(StandardValue/text())[1]', 'VARCHAR(MAX)') AS StandardValue
                    ,col.value('(ActualProvidedByCompany/text())[1]', 'VARCHAR(MAX)') AS ActualProvidedByCompany,
                    @TickerID AS TickerID
                FROM @BogyXML.nodes('/Root/PeriodicalData') AS tab (col)    

This way would work REPLACE(col.value('(Section/text())[1]', 'NVARCHAR(MAX)'),'''','''''')
when i will query the same data after insert then single quote would show in data or double quote ? basically i want single quote should be shown in data when i will query.

please give me guidance. thanks

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Viorel 110.8K Reputation points
    2020-11-28T19:58:24.65+00:00

    I think that it seems to work correctly, but you must not do this.

    If the XML text is like “John's Section”, it should be left unchanged; then SELECT will display “John's Section” in Management Studio.

    0 comments No comments

  2. Erland Sommarskog 100.1K Reputation points MVP
    2020-11-28T20:01:24.553+00:00

    . i tried this way.....please tell me does it work properly?

    Why don't you test it? You probably know your requirements better than we do.

    I can note, though, that your replace string does not include anything I would call a double quote, that is the character ". But maybe you want double single quotes, ''?

    0 comments No comments

  3. MelissaMa-MSFT 24,176 Reputation points
    2020-11-30T07:05:24.243+00:00

    Hi @Sudip Bhatt ,

    We recommend you to post your xml and expected output so that we could check further.

    Sometimes there is no need for us to replace the single quote (') with double quote('').

    You could refer below example:

    DECLARE @xmlDoc2 XML  
    SET @xmlDoc2 = '<PERSONS>  
      <PERSON>  
        <ID>1</ID>  
        <name>Eric''s</name>  
      </PERSON>  
      <PERSON>  
        <ID>2</ID>  
        <name>Zhang''s</name>  
      </PERSON>  
    </PERSONS>'  
      
     SELECT REPLACE(col.value('(name/text())[1]', 'NVARCHAR(MAX)'),'''','''''') AS name  
    FROM @xmlDoc2.nodes('/PERSONS/PERSON') AS tab (col)       
    --Eric''s  
    --Zhang''s  
      
     SELECT col.value('(name/text())[1]', 'NVARCHAR(MAX)') AS name  
    FROM @xmlDoc2.nodes('/PERSONS/PERSON') AS tab (col)    
    --Eric's  
    --Zhang's  
    

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Hot issues November--What can I do if my transaction log is full?
    Hot issues November--How to convert Profiler trace into a SQL Server table

    0 comments No comments