Escape characters in regex passed to SQL (Maria DB)

[jwaligora]

Hi,

I need the following line of syntax passed in an SQL call (in QV script) to Maria DB

REGEXP_REPLACE(myText, '\\\\(?=[\'|"|\\\\])', '')

I seem to be having trouble with getting the quotes and escapes right. I tried a number of things, including variables and variables in combination with chr(39), but so far nothing has worked. 

Note that my version of Maria DB does NOT recognize ASCII codes i.e. code of the sort:  

select concat('x','Y',chr(39));

The closest I've gotten was probably with this variable:

let vRegexp =  chr(39) & '\\\\(?=[\' & chr(39) & '|"|\\\\])' &  chr(39);

but it still somehow fails on evaluation. Maria DB error codes are not being very helpful as they basically fail to detail the error and instead they just redirect me to the manual. 

The syntax (when successfully passed to the DB) is used to purge backslash (escape characters) from text stored in myText field, provided the backslash is followed by one of {', ", \} , such that 

\"This is a \'pretty\' picture of a \\ character\".

becomes

"This is a 'pretty' picture of a \ character.

If anyone has a comparable environment to experiment on,  please let me know what you come up with.

Thanks,
J.

Tags: regex,  mariadb,  escape,  ascii,  syntax,  sql,  script

[marcus_sommer · Accepted solution]

I'm not sure if the cause of your issue is on the Qlik side. Thinkable is also that your used driver couldn't handle it and caused directly the error or changed the string in some way so that it becomes invalid. Excluding this possibility could be expensive by looking for a different driver or using the same driver from another tool to execute such query.

Nevertheless it looked that the char chr(39) which is default-char to wrap string-values in Qlik isn't properly applied. If a string-value itself contained a chr(39) it collided with the outer quotes-wrapping and needs therefore be doubled - means something like: 'it''s a beautiful day ...' - to return a valid string.

Beside this I would tend to make these transformations within Qlik. Qlik hasn't a native support of regex but with the various available string-functions should be nearly everything feasible. Even more practically is often the use of an appropriate mapping - in your case maybe something like this:

m: mapping load * inline [

m: mapping load * inline [
lookup	return
\}	}
\{	{
\'	'
\"	"
\,	,
\\	\
] (txt, delimiter is \t);

t: load *, mapsubstring('m', Field) as Field2;
sql select * from db;

 
- Marcus

[jwaligora]

Hi Marcus,

I have zero excuses for using nested Replace() instead of a map.

Thank you for suggesting it!

J.