Regex for SQL Query

Question

Hello together I have the following problem:
I have a long list of SQL queries which I would like to adapt to one of my changes. Finally, I have a renaming problem and I'm afraid I want to solve it more complicated than expected. The query looks like this:

INSERT member (member, prename, name, street, postalcode, town, tel1, tel2, fax, bem, anrede, salutation, email, name2, name3, association, project) VALUES (2005, N'John', N'Doe', N'Street 4711', N'1234', N'Town', N'1234-5678', N'1234-5678', N'1234-5678', N'Leader', NULL, N'Dear Mr. Doe', N'a@b.com', N'This is the text i want to delete', N'Name2', N'Name3', NULL, NULL);

In the "Insert" there was another column which I removed (which I did simply via Notepad++ by typing the search term - "example, " - and replaced it with an empty field. Only the following entry in Values I can't get out using this method, because the text varies here. So far I have only worked with the text file in which I adjusted the list of queries.

So as you can see there is one more entry in Values than in the insertions (there was another column here, but it was removed by my change).
It is the entry after the email address. I would like to remove this including the comma (N'This is the text i want to delete',).

My idea was to form a group and say that the 14th digit after the comma should be removed. However, even after research I do not know how to realize this.

I thought it could look like this (tried in https://regex101.com/)

VALUES\s?\((,) something here

Is this even the right approach or is there another method? I only knew Regex to solve this problem, because of course the values look different here. And how can I finally use the regex to get the queries adapted (because the queries are local to my computer and not yet included in the code).

---

Short summary:

Change the query from

VALUES (... test5, test6, test7 ...)

To

VALUES (... test5, test7 ...)

Answer 1

As per my comment, you could use find/replace, where you search for:

(\bVALUES +\((?:[^,]+,){13})[^,]+,

And replace with $1

See the online demo

• ( - Open 1st capture group.
  • \bValues +\( - Match a word-boundary, literally 'VALUES', followed by at least a single space and a literal open paranthesis.
  • (?: - Open non-capturing group.
    • [^,]+, - Match anything but a comma at least once followed by a comma.
    • ){13} - Close non-capture group and repeat it 13 times.
  • ) - Close 1st capture group.
• [^,]+, - Match anything but a comma at least once followed by a comma.

Answer 2

You may use the following to remove / replace the value you need:

Find What: \bVALUES\s*\((\s*(?:N'[^']*'|\w+))(?:,(?1)){12}\K,(?1)
Replace With: (empty string, or whatever value you need)

See the regex demo

Details

• \bVALUES - whole word VALUES
• \s* - 0+ whitespaces
• \( - a (
• (\s*(?:N'[^']*'|\w+)) - Group 1: 0+ whitespaces and then either N' followed with any 0 or more chars other than ' and then a ', or 1+ word chars
• (?:,(?1)){12} - twelve repetitions of , followed with the Group 1 pattern
• \K - match reset operator that discards the text matched so far from the match memory buffer
• , - a comma
• (?1) - Group 1 pattern.

Settings screen: