Storing Single Quotes in Varchar Variable SQL Server 2008

Storing single quotes in varchar variable SQL Server 2008

Like this. Yes Oded is correct. The proper terminology for this is 'escaping'. You can escape a single quote ' by doubling it up ''

DECLARE @codes1 varchar(50), @codes2 varchar(50)
SET @codes1 = '''abc'', ''def'', ''ghi'''
SET @codes2 = '''jkl'', ''mno'', ''pqr'''

How do I escape a single quote in SQL Server?

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.

Using a string of quoted values in a variable for a SQL WHERE CLAUSE

It doesn't work because the IN operator expects a list of items - here strings.

What you're supplying with your @ListValues variable however is a single string - not a list of strings.

What you could do is use a table variable and store your values in it:

DECLARE @ListOfValues TABLE (ItemName VARCHAR(50))

INSERT INTO @ListOfValues(ItemName) 
VALUES('aaa'), ('bbb'), ('ccc')

SELECT *    
FROM TABLEA 
WHERE FIELD1 IN (SELECT ItemName FROM @ListOfValues)

How to insert a value that contains an apostrophe (single quote)?

Escape the apostrophe (i.e. double-up the single quote character) in your SQL:

INSERT INTO Person
    (First, Last)
VALUES
    ('Joe', 'O''Brien')
              /\
          right here  

The same applies to SELECT queries:

SELECT First, Last FROM Person WHERE Last = 'O''Brien'

---

The apostrophe, or single quote, is a special character in SQL that specifies the beginning and end of string data. This means that to use it as part of your literal string data you need to escape the special character. With a single quote this is typically accomplished by doubling your quote. (Two single quote characters, not double-quote instead of a single quote.)

Note: You should only ever worry about this issue when you manually edit data via a raw SQL interface since writing queries outside of development and testing should be a rare occurrence. In code there are techniques and frameworks (depending on your stack) that take care of escaping special characters, SQL injection, etc.

How to put single quotes around variables in a dynamic query

You would have to place a quote in between the quotes, but escape it so it doesn't break your code. It would look like the following:

SET @Query = @Query + ' WHERE ' + '' + @param + ' ' + @operator + ' ' + '\'' + @val + '\'' ;

Edit: Eric Anderson's answer works as well. Take from the MySQL 5.0 Manual

A “'” inside a string quoted with “'” may be written as “''”.

---

Related Topics