CODEWITHSUNDEEP
sql-serverwhere-clause
KvothesLute
KvothesLute

Reputation: 195

Unable to get where clause to work with text containing a single quotation

I am working with some geo-spatial (string) data that i have been saving into an SQL server database, whenever my data has had a single quote within the string i am saving, i have tried to escape this by padding the single quote. Though this allows me to save the text into my database, when i try to use a where clause to find locations which have been had an escape sequence applied to them, the where clause does not seem to pick them up.

For example SELECT * from tb_test WHERE Address = 'Abbey Gardens, St John''s Wood, London, NW8'; Will produce an empty table, even if this record exists. The only want i have been able to get this to work was by using LIKE to match the pattern of my text which does not include the quotation (see below). SELECT * from tb_test WHERE Address LIKE 'Abbey Gardens'

Is there any reason as to why SQL does not pick up on the full Address ? and if so how can i get it to?

Upvotes: 0

Views: 284

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46233

SELECT * from tb_test WHERE Address = 'Abbey Gardens, St John''s Wood, London, NW8'; Will produce an empty table, even if this record exists.

The quotes are proper in the literal so it seems the actual column value does not match the literal value. This could be due to trailing whitespace (e.g. tab, newline, etc.). Check the raw binary value of the column for unexpected characters:

SELECT CAST(Address AS varbinary(MAX)), *
FROM tb_test
WHERE Address LIKE 'Abbey Gardens%';

Upvotes: 1

Related Questions