How do I escape a single quote in SQL Server?

To escape a single quote in SQL Server, you use two single quotes (''). This tells SQL Server to treat the two quotes as a single literal quote within the string. Here’s how it works in different scenarios:

1. Basic Escaping in String Literals

Replace each single quote in the string with two single quotes.

Example:
Insert the name O'Neil into a table:

INSERT INTO Customers (Name) VALUES ('O''Neil');

Result:
The value O'Neil is stored correctly.

2. Escaping in Dynamic SQL

When building dynamic SQL strings, ensure quotes are properly escaped.

Example:

DECLARE @Name NVARCHAR(50) = 'O''Neil';
DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM Customers WHERE Name = ''' + @Name + '''';
EXEC sp_executesql @SQL;

Output:
The query becomes:

SELECT * FROM Customers WHERE Name = 'O''Neil';

3. Using CHAR(39) for ASCII Representation

You can use CHAR(39) (ASCII code for a single quote) for programmatic escaping.

Example:

SELECT 'O' + CHAR(39) + 'Neil'; -- Returns: O'Neil

4. Edge Cases

• String starts/ends with a quote:

  -- String: 'Quote at start
  SELECT '''Quote at start'; -- Result: 'Quote at start

  -- String: Quote at end'
  SELECT 'Quote at end'''; -- Result: Quote at end'

• Empty string with a quote:

  SELECT ''''; -- Result: '

5. Best Practices

• Use Parameterized Queries (to avoid manual escaping and SQL injection):

  // C# Example with parameters
  using (SqlCommand cmd = new SqlCommand("INSERT INTO Customers (Name) VALUES (@Name)", connection)) {
      cmd.Parameters.AddWithValue("@Name", "O'Neil");
      cmd.ExecuteNonQuery();
  }

• Avoid Dynamic SQL unless necessary. If used, always escape quotes properly.

Summary Table

Scenario	Example	Escaped Result
Basic string	O'Neil	O''Neil
Dynamic SQL	'O''Neil'	'O''Neil'
ASCII representation	CHAR(39)	'
String starting/ending with '	'Quote at start	'''Quote at start

By doubling single quotes (''), you ensure SQL Server interprets them as literal quotes in strings. Always prefer parameterized queries to minimize risks and simplify code!