Dealing with single Quote in SQL Query
Hi Guys
how do I deal with single Quote inside string in an SQL query?
currently this type of query is failing with an error asking me for a closing quote :
Select * from cnd.Facilities where name like ''%Grill's BBQ%'
Thanks
Product version: Caché 2014.1
There's two ways around this:
repeating like this? didn't work
it looks like you're putting double quotes not two single quotes?
Lose one of the single quotes where my arrow is (there was a typo in my first reply).
That way your final line should be:
like '%Forceps McGill''s Neonatal NETS%' and i.Active = 1
Thanks but that will not return any records, knowing there a couple
The issue of no results being returned is likely elsewhere in your query.
To test this, I created a basic table with the following:
CREATE TABLE Demo.BBQ (
Name varchar(100),
Type varchar(50),
isActive bit
)
And I then added a few rows:
Insert into Demo.BBQ (Name, Type, isActive)
VALUES('Super Grill''s BBQ Hut','Outdoor',1)
Insert into Demo.BBQ (Name, Type, isActive)
VALUES('Bobs BBQ Bistro','Indoor',1)
Insert into Demo.BBQ (Name, Type, isActive)
VALUES('Rubbish Grill''s BBQ Resort','Not Known',0)
This then gave me a table that looks like this (note that the double single quotes used in the insert are inserted as a single quotes into the table):
If I then run a query using the like function:
And if I want to exclude the inactive location:
The use of doubling up a single quote to escape the character is not a Intersystems specific approach, but is generally a standard SQL way of escaping the single quote.