Replacing numbers in SQL Statement using RegEx

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.

image

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)

Hi @gcfleury,

You can use this expression in the String Manipulation node:

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

:blush:

4 Likes

Hi armingrudd,

That worked very fine, thank you so much!

1 Like

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.