How to keep backslashes in Data Base Queries

 

 

When Parsing Strings with REGEXP_SUBSTRING in a Database Query Node the Regular Expressions might contain Backslashes \

The Statement Found at the output of the Query Node properly contains the Backslashes:

Simplest example: After an established BD-Connection to an Oracle DB:

I append a "Database Table Selector" Node with the statement:

SELECT REGEXP_SUBSTR('{"xy" : 34}','("xy"\s:\s)([0-9]+)',1,1,NULL,2) AS "XY" FROM DUAL

The Connection in the Output looks good:

SELECT REGEXP_SUBSTR('{"xy" : 34}','("xy"\s:\s)([0-9]+)',1,1,NULL,2) AS "XY" FROM DUAL

and I get 34

Now if I append a Database Query Node:

with the statement:

SELECT * FROM #table#

the Connection in the Output is:

SELECT * FROM (SELECT REGEXP_SUBSTR('{"xy" : 34}','("xy"s:s)([0-9]+)',1,1,NULL,2) AS "XY" FROM DUAL) 

and I get nothing back.

Inspection of the last statment shows that the backslashes in the Regular Expression are lost.

How can I keep them? I am currently using KNIME 2.12.2

Hi,

this was a bug that was fixed with version 3.1.2 (AP-5757). So please update your KNIME version if possible. As a workaround it might also work if you quote the \ with another \ but I'm not sure about it since I don't have an old version at hand right now to check.

Bye

Tobias

Hi,

another workaround would be to use the SQL Extract/Inject nodes (see attached workflow) if quoting doesn't work and you can not upgrade to 3.1.2.

Bye

Tobias