SQL Server Performance Forum – Threads Archive
Character comparison with single quotesThe column ‘parameters’ in table ‘tbl’ has this data : ID1 = ”sub20202467” and ID2=”3” The following query returns results:
Select * from tbl where Parameters like ‘ID1 = ”%’ but this one doesn’t:
Select * from tbl where Parameters = ‘ID1 = ”sub20202467” and ID2=”3”’ neither does this one:
Select * from tbl where Parameters = ‘ID1 = ”s%’
Apparently, the single quotes are creating a problem. But they cannot be avoided inside the data.
The datatype of the column can be either text/varchar. Please help.
Thanks in advance.
If you have to include a single quote inside your filter string, you must double it up – this is the standard rule. Say you have a column value of McDonald’s, and you want to filter on that string, then your query must be: SELECT col FROM tbl WHERE col = ‘McDonald”s’ ******* If you send the filter value as a parameter to a stored procedure like this one … CREATE PROC dbo.test (@Filter VARCHAR(100))
SELECT col FROM tbl WHERE col = @Filter
GO … then you need to take care of doubling up the single quote in the call: EXEC dbo.test ‘McDonald”s’ ******* Anyway – if you would try without the doubled single quote, you’d get errors: EXEC dbo.test ‘McDonald’s’ Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ‘s’.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ‘
but the column data already contains 2 single quotes(paired).
The actual data is : ID1 = ”sub20202467” and ID2=”3” what should be done in that case?
Alright, <br />it just clicked to me (although, too late <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /> )<br /><br />this is what works: <br /><u>Select * from tbl where Parameters = ‘ID1 = ””sub20202467”” and ID2=””3””'</u><br /><br />I placed 2 single quotes for each 1 in the string.<br /><br />Thanks, anyways.
It can get really messy if you have to do this in dynamic SQL.