How to insert a sql command in SQL Server with apostrophe character

Question

I've created a script to export the content from a table to generate another script to insert on another database. On a specific field, I have a SQL statement saved from a application in this format:

(...other fields values...),'Select case 
    when @myVar in ('1','4') then '1'
    when @myVar in ('18','20') then '2'
    when @myVar = '5' then '5'
    when @myVar = '8'  then '6'
    when @myVar = '21' then '7'
    when @myVar = '16' then '9'
    when @myVar = '7' then '10'
    when @myVar in ('22','34') then '11'
    when @myVar = '11' then '12'
    when @myVar = '10' then '13'
    when @myVar = '9' then '14'
    when @myVar = '13' then '15'
    when @myVar = '3' then '20'
    when @myVar = '17' then '22'
    else '1'
end', (...other fields values...)

You may notice that the inner apostrophe is crashing the script.

How I can insert the original string as is?

I trying use replace with many ways but I can not get it to succeed.

Why are you saving this into the database? This is a sign that something is far left of center. If you want to include apostrophes in a string you have to escape them by making every one of them two apostrophes. So you need '' instead of ' everywhere inside your string value. — Sean Lange, Dec 20 '16 at 19:54
Possible duplicate of [How do I escape a single quote in SQL Server?](http://stackoverflow.com/questions/1586560/how-do-i-escape-a-single-quote-in-sql-server) — dfundako, Dec 20 '16 at 19:54

score 0 · Answer 1 · answered Dec 20 '16 at 20:00

0

Thanks for your time and interest. I try use replace(@myvalue, '''', '''''''') and it works fine. I'm sorry...

answered Dec 20 '16 at 20:00

GustavoAdolfo

361
1
9
23

score -1 · Answer 2 · answered Dec 20 '16 at 19:58

Lose the SELECT statement and just use a CASE:

SELECT
    OtherStuff
    ,CASE @myVar 
        WHEN '4' THEN '1'
        WHEN '18' THEN '2'
        WHEN '20' THEN '2'
        WHEN '5' THEN '5'
        WHEN '8' THEN '6'
        WHEN '21' THEN '7'
        WHEN '16' THEN '9'
        WHEN '7' THEN '10'
        WHEN '22' THEN '11'
        WHEN '34' THEN '11'
        WHEN '11' THEN '12'
        WHEN '10' THEN '13'
        WHEN '9' THEN '14'
        WHEN '13' THEN '15'
        WHEN '3' THEN '20'
        WHEN '17' THEN '22'
        ELSE '1'
    END,
    (...other fields values...)

How to insert a sql command in SQL Server with apostrophe character

2 Answers2