Hi,
Is there a simple way to wrap a single quote mark in a string for the SQL Executor?
Occasionally we will have a name that contains a single quote i.e St Paul's and these are being passed in as parameters into a executor for a Postgres database.
thanks
+60You can escape the ' by adding the escape character, a ', in front of it.
SELECT * FROM "public"."Plaats" WHERE "naamNL" = '''s-Hertogenbosch'Selects the value 's-Hertogenbosch
It depends on the underlying SQL server.
In Oracle you can use the "q" mechanism to specify an alternative quote delimiter, e.g.
select q'#Oracle's quote operator#' from dual;In SQL Server it's possible to play around with these settings:
SET QUOTED_IDENTIFIER ON/OFFBut I find it slightly hacky, for lack of a better word. You may be better off looking into the solution suggested by @redgeographics.
In Postgresql there's dollar quoting which I find pretty neat, e.g.
SELECT $$hello's the name of the game$$
+60You can escape the ' by adding the escape character, a ', in front of it.
SELECT * FROM "public"."Plaats" WHERE "naamNL" = '''s-Hertogenbosch'Selects the value 's-Hertogenbosch
Just read @david_r's answer, prompting me to point out I'm using PostGIS
+22Another simple way of doing this (I used Oracle) is replacing the single ' by two single quotes '', using a StringReplacer:
