9

I'm trying to use the Data Defined symbology in QGIS ver 2.2. Some of my data has single quotes/apostrophes.

Since the string must use single quotes, is there an escape option I can use for the field calculator or for the data defined settings field?

So, I'm trying to use something like this:

CASE
WHEN "NAME" = 'WILSON'S' THEN '0,75,224'
END

How do you build an expression around a string that may have a single quote or apostrophe?

Thanks. QGIS Valmiera 2.2 Windows 7 - 32 bit

Babel
  • 71,072
  • 14
  • 78
  • 208
mike
  • 981
  • 11
  • 17

2 Answers2

13

Using the escaping by simply doubling the single quote works for me:

CASE
WHEN "NAME" = 'WILSON''S' THEN '0,75,224'
END

Double quotes in the field value don't need escaping:

CASE
WHEN "NAME" = 'WILSON"S' THEN '0,75,224'
END

and I'm not sure if they are allowed in field names. Surely not in shapefiles.

Works at least on Windows.

AndreJ
  • 76,698
  • 5
  • 86
  • 162
2

You can always mask every character, including single or double quotes, by using their ascii-code:

  • char(39) returns a single quote: '
  • char(34) returns a double quote: "

To find the ascii code, simply look for it with ascii(): ascii('a') returns 97. To find the ascii code for single quotes, enter ascii('''') - this returns 39.

Thus you can also use:

CASE
WHEN "NAME" = 'WILSON' || char(39) || 'S' THEN '0,75,224'
END

char even works for Unicode codes:

Babel
  • 71,072
  • 14
  • 78
  • 208
  • 1
    Thank you for this. I used it in model builder where I added a string model parameter to PostgreSQL execute and load SQL algorithm. Double quotes did not work there. – Trnovstyle Jun 28 '22 at 09:16