' ' inside of " " in SQL Server

Question

In this SQL query

INSERT INTO RESULT_FILTER (RESULT_FILTER_NAME, RESULT_FILTER_SQL)
VALUES ('German Power Trades flagged red', 
        '" AND worst_status = 'R' AND country = 'DE' AND commodity = 'EL'"');

There is the problem that SQL Server can't handle the 'R' and 'DE' and 'EL' inside of the "AND worst_status='R' AND country='DE' AND commodity='EL'"'

This error occurs:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'R'.

Is there another syntax I have to use inside the quote to let SQL Server see it just as a quote? I am using Microsoft SQL Server Management Studio 2017

Possible duplicate of [How do I escape a single quote in SQL Server?](https://stackoverflow.com/questions/1586560/how-do-i-escape-a-single-quote-in-sql-server) — Diado, Jul 31 '18 at 10:00
How do you insert that? In code? If so, use prepared statements. — juergen d, Jul 31 '18 at 10:00

Yogesh Sharma · Accepted Answer · 2018-07-31T10:09:06.493

3

You need to double-up the single quotes :

INSERT INTO RESULT_FILTER (RESULT_FILTER_NAME, RESULT_FILTER_SQL)
        VALUES('German Power Trades flagged red', '" AND worst_status=''R''
                 AND country=''DE'' AND commodity=''EL'' "');

edited Jul 31 '18 at 10:09

answered Jul 31 '18 at 10:01

Yogesh Sharma

49,870
5
26
52

3

"double quote" is a fairly common name for the `"` character. I'd instead say something like "You need to double-up the single quotes", which is what you've done in your code. – Damien_The_Unbeliever Jul 31 '18 at 10:07
@Damien_The_Unbeliever. . . Noted. – Yogesh Sharma Jul 31 '18 at 10:09

score 1 · Answer 2 · answered Jul 31 '18 at 10:15

In a real world environment you are unlikely to write those values literally. If you really do, then you would need your single quotes doubled. For the real world scenario however, you would be using parameters which also take care of this issue:

declare @filterName nvarchar(200) = N'German Power Trades flagged red'
declare @filterSQL nvarchar(4000) = N'AND worst_status=''R''
      AND country=''DE'' 
      AND commodity=''EL''' 
-- @filterName and @filterSQL would come from client
INSERT INTO RESULT_FILTER (RESULT_FILTER_NAME, RESULT_FILTER_SQL)
VALUES (@filterName, @filterSQL);

' ' inside of " " in SQL Server

2 Answers2