Skip to main content
Question

single quote in name used as parameter - SQL Executor

  • December 5, 2019
  • 4 replies
  • 342 views

Forum|alt.badge.img

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

4 replies

redgeographics
Celebrity
Forum|alt.badge.img+49

You 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


david_r
Celebrity
  • December 5, 2019

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/OFF

But 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$$

 


redgeographics
Celebrity
Forum|alt.badge.img+49
redgeographics wrote:

You 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


erik_jan
Contributor
Forum|alt.badge.img+17
  • Contributor
  • December 5, 2019

Another simple way of doing this (I used Oracle) is replacing the single ' by two single quotes '', using a StringReplacer:

 


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings