Escaping special characters in a query


#1

I’ve been trying to escape the ' character in a number of queries. It turns out there are counties in the USA like O’Brien county Iowa that are messing up some of my queries.

The normal way for me to escape a character like that was a simple forward slash \. When I try select count(*) from mytable where county = 'O\'Brien' I still get an error like Parse failed: Encountered “Brien” because the single quote closed the value without respect to the forward slash.

I’ve also tried url encoding the string since it’s passing through an http request anyways, but still not luck.

Please tell me the correct way to escape characters in a query on MapD.

Thanks.


#2

Hi

We use a second ' quote as the escape for single quote in a string

example

mapdql> create table t1 (t1 text);
mapdql> insert into t1 values ('fred');
mapdql> insert into t1 values ('o''brien');
mapdql> insert into t1 values ('smith');
mapdql> select * from t1 ;
t1
fred
o'brien
smith
mapdql> select t1 from t1 where t1='fred' ;
t1
fred
mapdql> select t1 from t1 where t1='o''brien' ;
t1
o'brien
mapdql>

regards