Extract components and add to string

Question

I have many insert statements in this format:

INSERT INTO MY_TABLE (MY_ID, MY_NAME) VALUES(l_some_variable,'SOME NAME');

I need to change it so they take this form:

INSERT INTO MY_TABLE (MY_ID, MY_NAME) VALUES(l_some_variable, 'SOME NAME') RETURNING MY_ID INTO t_capital('l_some_variable ---- SOME NAME'); 

essentially needing to add to the end: RETURNING MY_ID INTO t_capital('[variable_name] ---- [name of the string]')

There are 10's of thousands of statements that make this very tedious if done manually. I am very bad at regex and haven't been able to figure it out in notepad++.

Answer 1

• Ctrl+H
• Find what: INSERT INTO MY_TABLE \(MY_ID, MY_NAME\) VALUES\((\w+),'([^']+)'\)\K(?=;)
• Replace with: RETURNING MY_ID INTO t_capital\('$1 ---- $2'\)
• TICK Wrap around
• SELECT Regular expression
• UNTICK . matches newline
• Replace all

Explanation:

INSERT INTO MY_TABLE \(MY_ID, MY_NAME\) VALUES\(
(\w+)           # group 1, 1 or more word character
,'              # literally
([^']+)         # group 2, 1 or more any character that is not single quote
'\)             # literally
\K              # forget all we have seen until this position
(?=;)           # positive lookahead, make sure we have a semicolon after

Replacement:

RETURNING MY_ID INTO t_capital\('$1 ---- $2'\)      # in Notepad++, parenthesis hav2 to be escaped in replacement part

Screenshot (before):

Screenshot (after):