DEV Community


How to add single quote in SQL query string

Developer | Blogger | Learner
・2 min read

In this post, we will learn about How to add single quote in sql query string.

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
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 @test as NVARCHAR(400)

SET @text= ‘My Parent’s car is broken.’
'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.

'My Parent’’s car is broken.'

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.

See more examples
Use two single quotes: ''

select *, 'INSERT INTO San_Endereco (Endereco_Id, Logradouro_Id, Bairro_Id, CEP, Logradouro, Livre) VALUES

(''' + CAST(Endereco_Id as varchar) + ''','''

  • CAST(Logradouro_Id as varchar) + ''','''
  • CAST(Bairro_Id as varchar) + ''','''
  • CAST (CEP as varchar) + ''','''
  • CAST(Logradouro as varchar) + ''','''
  • CAST(Livre as varchar) + ''')''' as teste FROM San_Endereco

Hope this post be thankful and useful to you
Thank You

Discussion (0)