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
Transcript:
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.
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.
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.
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.
Leave a Reply