Single Quote Usage in Query

Question

Post reply

Single Quote Usage in Query

a-malik

SSC Journeyman

Points: 93
More actions
December 8, 2006 at 3:42 am

#90860

Dear All,
I have been trying to run a query, for retrieving an id against a string containing quotes in it.
The query is:
Select intpkStatus, strStatus from Status where sStatus = 'Won"t Fix'
In this case "Won't Fix" has a single quotes within it. I have checked it over at http://www.sql-tutorial.com/sql-where-sql-tutorial/ and it tells that double quotes should be used. However its not working, can you please help me on that.
Regards and Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18838 More actions · Answer 1

'Won"t Fix' should be 'Won''t Fix'

ie Embed two single quotes, not one double quote.

eg SELECT 'Won''t Fix' AS STRING

STRING

---------

Won't Fix

(1 row(s) affected)

a-malik SSC Journeyman Points: 93 More actions · Answer 2

Well I am using Won't Fix in the where clause, can you please guide my what should be done in that case.

DaveItz SSCrazy Points: 2146 More actions · Answer 3

To the eye, 'Won''t Fix' with two single quotes inside (the proper way to do it) appears to be 'Won"t Fix' with a double quote inside. but if we change the font to a larger monospaced font, you can see that 'Won''t Fix' is not the same thing as 'Won"t Fix'.

Using two single quotes, in this fashion, is called escaping the quote (differentiating it from the closing quote.) Also, depending on your QUOTED_IDENTIFIER settings you may be able to do "Won't Fix" instead. Though, it is probably best to use the syntax that will always work regardless of your settings.

a-malik SSC Journeyman Points: 93 More actions · Answer 4

Quite a good point identified, that I was not focusing on all. by the way QUOTED_IDENTIFIED settings -> can you please guide me through that.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

I would suggestyou don't focus on that point at the moment. The best solution is to use a double single quote. And an even greater solution would be to use stored procedure where this problem just would not occur .