How to Properly Escape Single Quotes in SQL Server Text Inserts?

Escape text inserted single quotes in SQL Server

In SQL Server, inserting text containing single quotes can be tricky due to potential syntax errors. To solve this problem, a suitable escaping technique is needed.

To escape single quotes in text strings in SQL Server, they must be doubled. For example, consider the following example:

<code class="language-sql">INSERT INTO my_table VALUES('hi, my name''s tim.');</code>

In this example, the single quotes in the string are escaped by doubling them. This allows SQL Server to correctly interpret the string without mistaking it for query syntax.

Here’s a more detailed explanation of another example:

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

INSERT INTO @my_table VALUES ('hi, my name''s tim.')

SELECT * FROM @my_table</code>

This code will create a table named @my_table, which contains a column named value, which can store strings. It then inserts a line of text string containing single quotes escaped using double quotes. Finally, it selects values ​​from the table to display the results.

After execution, you will see the following output:

<code>value
==================
hi, my name's tim.</code>

This confirms that the single quotes in the text string have been successfully escaped, allowing it to be correctly inserted and displayed in the database.

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