New post
Question
Rochdi Badis · Jul 20, 2022

Dealing with single Quote in SQL Query

#Ensemble

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
0
0 203
Discussion (5)1
Log in or sign up to continue
Julian Matthews · Jul 20, 2022

There's two ways around this:

  1. If this is something you're running one off in, say, the sql query tool within the management portal, then you will want to repeat the single quote to be "Select * from cnd.Facilities  where name like ''%Grill''s BBQ%'" and this will escape the single quote.
  2. If this is a query being called within your code and the value could be anything passed to it, then you will want to look to use parameterised SQL queries. An example can be found for SQL adapters here.
0 0
Rochdi Badis · Jul 20, 2022

repeating like this? didn't work

it looks like you're putting double quotes not two single quotes? 

0 0
Julian Matthews · Jul 20, 2022

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

0 0
Rochdi Badis · Jul 20, 2022

Thanks but that will not return any records, knowing there a couple

0 0
Julian Matthews · Jul 21, 2022

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.

0 0