Do You Want to Learn SQL?

Take our free eight day course, where I'll teach you in simple to understand English all you need to get started learning SQL.


Free Beginners SQL Course!

How to include a single quote in a SQL query




In this video we’ll walk you through how to include a single quote in a SQL query; we’ll show how to escape the single quote, so it is treated as text rather than text delimiters.

Once you’ve gone through this article, I would recommend watching our next Essential SQL Minute continue to learn more about SQL Server!

Once you have watched the video check out the sample code below.  I’ve also included a transcript for you to use.

This is an SQL Minute on using a Single Quote in a SQL Query


Welcome to another essential SQL Minute. In this episode, we’re going to learn how to include a single quote in a SQL query. So when we’re creating queries that contain text, we use the single quote character to delimit the beginning and ending of our text value. For example, in this query, you can see where I have a single quote that is delimiting the beginning and end here of a text, which is a comma and space.

SELECT LastName + ', ' + FirstName as LastFirst
FROM   Person.Person

And I’m using it here to build an expression that will show the column as, FirstName, LastName. So when I run this, you’ll see where the values that I get back are, last name, comma, and first name.

Single Quote Query Example
Query Results

So this single quote works well. Where it breaks down though, is that if the text that I want to include in my value also includes a single quote, then I can run into issues, and I will show you that with an example.

Example SQL with Quotes

So here I have an example where I’ve declared some text, and I’m setting the text equal to my parent’s car is broken. And here you can see I have a possessive “s”, with a single quote.

DECLARE @text as NVARCHAR(400)
SET @text = 'My Parent's car is broken.'

And what’s happening here is just that, you’ll see some syntax errors, and the SQL is recognizing this first single quote as being a delimiter.

It thinks that the text part is my parent, and it doesn’t know what’s going on back here with the S, and then, the car is broken. It doesn’t understand that this quote, that’s really part of my phrase, is data, and that meant to be the delimiters for the statement.

Single Quotes Gone Wrong
Single Quotes Gone Wrong

To get around this, what we do is we use a fancy term. It’s called escaping the quote, and then in SQL, the convention to do that is to put another quote in front of it.

Escaped Single Quotes
Escaped Single Quotes

So now I have two single quotes, and as you can see, the whole thing has turned red, so it’s recognizing the whole thing as a text value. And it says, my parent’s car is broken. So this now is a proper statement.

Beware We Aren’t Using Double Quotes!

I want to point out that this is not a double quote. This is not the same as using the double quote marks here.

So see, if I run this it will be, my parent’s, double quote, the car is broken. That’s not what I want it to be. I want it to be single quotes, so I go like this and escape it. And if I was to do like, select, to show you what the value is. We can run this. You’ll see here where it says, my parent’s car is broken.

I’m going to take another example here where we are constructing an SQL statement. This is called dynamic SQL, and then I’m going to run it. I have a blog article on this if you’d like to look it up. And as you can see, we’re selecting the first name, last name, from a person where the last name like, and this is going be, “R%” and a first name like, “A%”. So it’s going to basically get everybody that has a last name that starts with R and a first name that starts with A.

DECLARE @statement as NVARCHAR(400)
SET @statement = 'SELECT FirstName, LastName
                  FROM   Person.Person
                  WHERE  LastName like ''R%'' AND FirstName like ''A%'' '

EXECUTE sp_executesql @statement

I can show you what this string looks like. It’s going to be set, let’s do, select, to show the string, and then we’ll execute it. So now let’s execute it. And you can see where I have the select statement that’s being given, and I’m just going to copy this text here.

Let’s see if I can get it into notepad so you guys can see what this looks like. Going to clean it up a little bit. So here’s what the actual constructed SQL looks like where it has the single quotes in it.

SELECT FirstName, LastName
FROM Person.Person
WHERE LastName like ‘R%’ AND FirstName like ‘A%’

I could literally take this now and run it if you want to see what that looked like. So if I went to a new query here, I could literally paste that in and execute it.

That’s the dynamic SQL part, because we actually built this piece right here using that text statement, so that’s kind of cool in itself, but the point I wanted to drive home is, is that this single quote here was actually part of our text data. What we had done here is escaped it, you can see where I have two single quotes.

And what gets tricky is at the end, if you were to look at it, you’ll see where there are actually three single quotes and it seems very strange. The reason is, is I have an escaped double, or single quote here so that we can delimit our query criteria. Of course, I need a final single quote to delimit the entire text string.

If you have this all munched together, sometimes it’s hard to read. So it’s just that at the end of the statement I can get away with adding space, and this kind of alerts you to the fact that this is for the SQL criteria, and then this is for the entire string.

Related Posts!

6 responses to “How to include a single quote in a SQL query”
  1. tarun

    thanks, nice documeny

  2. shyam

    good one

  3. sus

    if i need ‘ in frist of a value selected from query what can be done? Ex: SET @query = ‘select firstnmae, second name from users’;I need to see resut as ‘NaminDB,John when i do exec(@query)

  4. James Dinser

    Just to let you know I tried the extra single quote and it did not work for the following query in SQL 2012. I put the extra single quote between the n and ‘
    The error given is Incorrect syntax.

    RAISEERROR(‘Darn this didn’t work.’, 16, 1);

    RAISEERROR(‘Darn this didn”t work.’, 16, 1);

    1. James Dinser

      slight correction.

      I took the extra E out of RAISEERROR and the query still fails.

      1. If you look carefully, the single quote character you’re using is ’ is it where SQL Server uses ‘.

        RAISERROR(‘Darn this didn’t work.’, 16, 1); will cause a syntax error… since the second quote in “didn’t ” terminates the string.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

More from the blog

MySQL PostgreSQL SQLite SQL Server