Using RegEx lookaround expressions in a SQL statement

[KnimeUser1234]

Hey guys,

I want to use a negative lookahead expression to ensure that a particular keyword doesn’t appear within 3 characters of another keyword(s). The statements are running fine in text editors like Notepad++ but KNIME doesn’t seem to like them.

Here is the statement I’m trying to run:

SELECT * FROM #table# AS “table”
WHERE REGEXP_LIKE(“column1”, ‘.(keyword1|keyword2(?!.(.{0,3})keyword3.)', ‘i’)
OR REGEXP_LIKE(“column2”, '.(keyword1|keyword2(?!.(.{0,3})keyword3.)’, ‘i’)
OR REGEXP_LIKE(“column3”, ‘.(keyword1|keyword2(?!.(.{0,3})keyword3.)', ‘i’)
OR REGEXP_LIKE(“column4”, '.(keyword1|keyword2(?!.(.{0,3})keyword3.)’, ‘i’)
OR REGEXP_LIKE(“column5”, ‘.(keyword1|keyword2(?!.(.{0,3})keyword3.)', ‘i’)
OR REGEXP_LIKE(“column6”, '.(keyword1|keyword2(?!.(.{0,3})keyword3.)’, ‘i’)

This is consistently returning the error:

Invalid regular expression: ‘.(FGFR|TF53)(?!.(.{0,3})NEGATIVE.*)’, no argument for repetition operator: ?

I also had the same problem when I tried to use (?!) operator to enable case insensitivity, but was able to workaround this by using ‘i’ instead.

Any help would be fully appreciated.
Thanks!

[denisfi]

Hi!

Well, as i know about knime, something maybe missing. Did you use some especific node?

A tip from here is use DB Executor Node to execute SQL commands from inside your database.

Just connect your database and use this node after it… If you have more then 1 statement you cau use “;” to separate it…

I hope that can solve your questions.

Seeya,

Denis

[KnimeUser1234]

Hi Denis

Thanks for your reply! I’m using the DB Query node to execute SQL statements as I have multiple DB Tables being loaded in from different database sources which are joined into one and then queried on afterwards.

Would there be a workaround to execute the SQL statement inside a DB Query?

[denisfi]

Hi, whats your name? sorry…

DB Query just make a simple select statement for your DB. If you’d like to execute as a command from your DB, use this node to simplify you search and bring the result from your query, even from multiples DB sources. It’s for advanced programming codes.

As you can see, you can use “;” to separate and make some operations that DB Query can be more restrict.

Seeya,

Denis