How to Escape a Single Quote in SQL Server

← PrevNext →

You can easily escape a single quote in SQL Server by doubling it. Yes, it is that simple. Add two quotes ('') instead of a single quote and SQL Server will store the single quote along the string in the table.

While working on an e-commerce project recently, I came across a situation where I had to input a string like Exclusive Men's Wear in a textbox and store the value as it is in an SQL Server table. Now, look at the string value carefully; there is is a single quote within the value Men's.

The INSERT statement, after the app picks up the values from the textbox looked like this.

INSERT INTO myTable (Some_ID, Category) VALUES (6, 'Exclusive Men's Wear')

SQL Server will throw an error, since it does not see a closing bracket after the first single quote (i.e., after the letter n in Men's). Although, it’s a very common issue and I have faced it many times before, I made a mistake. The mistake was not doubling it up.

The above statement should be,

INSERT INTO myTable (Some_ID, Category) VALUES (6, 'Exclusive Men''s Wear')

See, now the string Men''s has two single quotes. This is fine. SQL Server will execute the statement and will insert the values in the table.

Remember, it is not a double quote, but two single quotes.

In case, you are extracting the values from an <input> box or a textbox using Asp.Net, you must replace the single quote with two quotes. Use the replace() method in Asp.Net to replace the quote.

The Textbox

<asp:TextBox ID="category" runat="server"></asp:TextBox>

C# Code

category.Text.Replace("'", "''").Trim();

Visual Basic Code

Trim(Replace(category.Value, "'", "''"))

Well, that’s it. Thanks for reading 🙂.

← PreviousNext →