How Do I Escape Single Quotes in SQL Server Inserts?

Handling Single Quotes in SQL Server Inserts

Inserting text data into SQL Server databases requires careful handling of special characters, particularly single quotes ('). Incorrectly handling these can lead to SQL syntax errors.

Consider this problematic SQL statement:

<code class="language-sql">INSERT INTO my_table VALUES ('It's a test.');</code>

The single quote in "It's" will prematurely terminate the string literal, causing an error.

The solution in SQL Server is simple: double the single quote. The corrected statement is:

<code class="language-sql">INSERT INTO my_table VALUES ('It''s a test.');</code>

This technique escapes the single quote within the string, allowing the entire string to be correctly interpreted.

Let's illustrate with a practical example using SQL Server (the version is not critical, this method works across versions):

<code class="language-sql">DECLARE @my_table TABLE (
    [value] VARCHAR(200)
);

INSERT INTO @my_table VALUES ('It''s a test with multiple ''quotes''.');

SELECT * FROM @my_table;</code>

The output will correctly display:

<code>value
------------------------------
It's a test with multiple 'quotes'.</code>

This demonstrates how doubling single quotes effectively handles them within SQL Server string literals during insertion. This approach avoids more complex parameterized query methods for this specific issue.

The above is the detailed content of How Do I Escape Single Quotes in SQL Server Inserts?. For more information, please follow other related articles on the PHP Chinese website!