Replacing numbers in SQL Statement using RegEx

[gcfleury]

Hi,

I’m trying to replace some integer values in a sql statement, right after the String “VALUES(” that corresponds to the private key. This private key has a length that comprises 1-6 or more numbers. The final result could be written in a file, like a CSV file.

Basic SQL statement:

INSERT INTO PLIND (SQ_PL, SEG_NEG, NM_PL, PLIN_SG_PL, ATIV_PL) VALUES(Integer, Integer, String, String, Char);

I need to replace it with an Oracle sequence, such as SEQ.NEXTVAL, like the following:

Original:
INSERT INTO PLIND (SQ_PL, SEG_NEG, NM_PL, PLIN_SG_PL, ATIV_PL) VALUES(41, 12, ‘First’, ‘FRS’, ‘S’);
Changed:
INSERT INTO PLIND (SQ_PL, SEG_NEG, NM_PL, PLIN_SG_PL, ATIV_PL) VALUES(SEQ.NEXTVAL, 12, ‘First’, ‘FRS’, ‘S’);

Original:
INSERT INTO PLIND (SQ_PL, SEG_NEG, NM_PL, PLIN_SG_PL, ATIV_PL) VALUES(554776, 29, ‘Second’, ‘SCD’, ‘N’);
Changed:
INSERT INTO PLIND (SQ_PL, SEG_NEG, NM_PL, PLIN_SG_PL, ATIV_PL) VALUES(SEQ.NEXTVAL, 12, ‘First’, ‘FRS’, ‘S’);

Here is the workflow, depicted below.

The question is: what regular expression should I use to have the desired results and with what knime component (String Manipulation or String Replacer).

The basic workflow is also attached here.

Thanks in advance.

RegEx Test.knwf (14.7 KB)

[armingrudd]

Hi @gcfleury,

You can use this expression in the String Manipulation node:

regexReplace($column1$, "(?<=VALUES\\()\\d+", "SEQ.NEXTVAL")

[gcfleury]

Hi armingrudd,

That worked very fine, thank you so much!