Replace all occurrences of a character between 2 words

vamshi reddy 0

I need to remove all [ and ] characters that occur between INSERT INTO and VALUES, but keep the remaining text and also don’t delete these characters that occur outside the 2 words. So select all text that starts with INSERT INTO and end with VALUES and remove [ and ] from only that text. Example:

FROM

INSERT INTO [emp1] ([id], [name], [age], [address])
VALUES (1, ‘john’, 23, ‘123 ad[ams street’) ;
INSERT INTO [emp2] ([id], [name], [age], [address])
VALUES (2, ‘jim’, 21, ‘123 dow]ning street’) ;
INSERT INTO [emp3] ([id], [name], [age], [address])
VALUES (3, ‘jack’, 20, ‘123 pa][rk street’) ;

TO

INSERT INTO emp1 (id, name, age, address)
VALUES (1, ‘john’, 23, ‘123 ad[ams street’) ;
INSERT INTO emp2 (id, name, age, address)
VALUES (2, ‘jim’, 21, ‘123 dow]ning street’) ;
INSERT INTO emp3 (id, name, age, address)
VALUES (3, ‘jack’, 20, ‘123 pa][rk street’) ;

Thanks in advance.

Alan Kilborn

@vamshi-reddy-0

It seems like a perfect chance to use the general regex replacement instructions for such a task found HERE.

vamshi reddy 0

Thanks Alan, going to try that now.

Alan Kilborn

@vamshi-reddy-0 said in Replace all occurrences of a character between 2 words:

Thanks Alan, going to try that now.

Let us know if you have trouble.
I tried it on your data and it seemed to work perfectly for your need.
Remember that you want to be in “Regular expression” Search Mode.

astrosofista

Hi @vamshi-reddy-0

As long as example data is representative of the job, the following regex will accomplish it. It takes a different approach than @Alan-Kilborn’s suggestion

Search: (?-s)(?<=VALUES )(.+)|\[|\]
Replace: $1

Put the caret at the very beginning of the document, select the Regular Expression mode and click on Replace All.

Take care and have fun!

vamshi reddy 0

@Alan-Kilborn said in Replace all occurrences of a character between 2 words:

@vamshi-reddy-0 said in Replace all occurrences of a character between 2 words:

Thanks Alan, going to try that now.

Let us know if you have trouble.
I tried it on your data and it seemed to work perfectly for your need.
Remember that you want to be in “Regular expression” Search Mode.

Took me a min but I’m guessing its the below, right?

for [ - (?-i:INSERT|(?!\A)\G)(?s:(?!VALUES).)*?\K(?-i:[)

for ] - (?-i:INSERT|(?!\A)\G)(?s:(?!VALUES).)*?\K(?-i:])

Alan Kilborn

@astrosofista

WHY would you do that?
We need to point posters to generic solutions.
Not reinvent the wheel each time.
:-(

vamshi reddy 0

@astrosofista said in Replace all occurrences of a character between 2 words:

Hi @vamshi-reddy-0

As long as example data is representative of the job, the following regex will accomplish it. It takes a different approach than @Alan-Kilborn’s suggestion

Search: (?-s)(?<=VALUES )(.+)|\[|\]
Replace: $1
Search: (?-s)(?<=VALUES )(.+)|\[|\]
Replace: $1
Put the caret at the very beginning of the document, select the Regular Expression mode and click on Replace All.

Take care and have fun!

Thank you kind sir, this worked too :)

vamshi reddy 0

@Alan-Kilborn said in Replace all occurrences of a character between 2 words:

@astrosofista

WHY would you do that?
We need to point posters to generic solutions.
Not reinvent the wheel each time.
:-(

I did give it a try and found a solution though :D

Alan Kilborn

@vamshi-reddy-0

There can be more than one solution to a problem, certainly.
You probably had it with where I had sent you, but it is hard to tell because this site gobbles up a backslash used before a [.

vamshi reddy 0

@Alan-Kilborn said in Replace all occurrences of a character between 2 words:

@vamshi-reddy-0

There can be more than one solution to a problem, certainly.
You probably had it with where I had sent you, but it is hard to tell because this site gobbles up a backslash used before a [.

Got it. It looks like it works though. And thank you for giving a template and not a direct solution, had to use a couple neurons :D

Alan Kilborn

@vamshi-reddy-0 said in Replace all occurrences of a character between 2 words:

And thank you for giving a template and not a direct solution, had to use a couple neurons

Yes, but it felt good, I’m sure!
And…you have a “tool” for next time you need to replace only within a delimited range!

Teach a man to fish…

vamshi reddy 0

@Alan-Kilborn said in Replace all occurrences of a character between 2 words:

@vamshi-reddy-0 said in Replace all occurrences of a character between 2 words:

And thank you for giving a template and not a direct solution, had to use a couple neurons

Yes, but it felt good, I’m sure!
And…you have a “tool” for next time you need to replace only within a delimited range!

Teach a man to fish…

Okay, so I tried it on a large SQL file and it takes out [ even before the first ever INSERT INTO occurrence.

What I used in search

(?-i:INSERT INTO|(?!\A)\G)(?s:(?!VALUES).)*?\K(?-i:\\[)

It replaced it in “SET IDENTITY_INSERT [db].[emp1] OFF;” for example which came before the INSERT INTO [emp1]

Alan Kilborn

@vamshi-reddy-0

I tried it on a large SQL file and it takes out [ even before the first ever INSERT INTO occurrence.

Hmm, I don’t see that behavior.

If I take your example, and copy one of its lines before any “INSERT INTO”… :

VALUES (1, ‘john’, 23, ‘123 ad[ams street’) ;
INSERT INTO [emp1] ([id], [name], [age], [address])
VALUES (1, ‘john’, 23, ‘123 ad[ams street’) ;
INSERT INTO [emp2] ([id], [name], [age], [address])
VALUES (2, ‘jim’, 21, ‘123 dow]ning street’) ;
INSERT INTO [emp3] ([id], [name], [age], [address])
VALUES (3, ‘jack’, 20, ‘123 pa][rk street’) ;

And I use this:

(?-i:INSERT INTO|(?!\A)\G)(?s:(?!VALUES).)*?\K(?-i:\\[|\\])

I obtain:

VALUES (1, ‘john’, 23, ‘123 ad[ams street’) ;
INSERT INTO emp1 (id, name, age, address)
VALUES (1, ‘john’, 23, ‘123 ad[ams street’) ;
INSERT INTO emp2 (id, name, age, address)
VALUES (2, ‘jim’, 21, ‘123 dow]ning street’) ;
INSERT INTO emp3 (id, name, age, address)
VALUES (3, ‘jack’, 20, ‘123 pa][rk street’) ;

Possible things I could be doing differently:

I have Wrap around ticked
I press the Replace All button

vamshi reddy 0

@Alan-Kilborn said in Replace all occurrences of a character between 2 words:

(?-i:INSERT INTO|(?!\A)\G)(?s:(?!VALUES).)*?\K(?-i:[|])

That worked perfectly. Possibly some backslash difference.

Alan Kilborn

@vamshi-reddy-0

Yea, like I said, backslash before [ or ] on this site is crazy hard to remember how to express.

Well, the good part is that it is solved for you.
And that satisfying clunk of that new tool as you drop it into your toolbox.

Thank you for being a “smart” regex questioner, willing to learn something – it is refreshing! :-)

vamshi reddy 0

@Alan-Kilborn said in Replace all occurrences of a character between 2 words:

@vamshi-reddy-0

Yea, like I said, backslash before [ or ] on this site is crazy hard to remember how to express.

Well, the good part is that it is solved for you.
And that satisfying clunk of that new tool as you drop it into your toolbox.

Thank you for being a “smart” regex questioner, willing to learn something – it is refreshing! :-)

God, its odd. I just undid the change, reused my 2 originals separately and it works. I have no idea why it did that funky stuff before. I’m sure its something I did (2.45am here in India).

Thank you for patiently answering sir, much appreciated 🙏

astrosofista

@Alan-Kilborn said in Replace all occurrences of a character between 2 words:

@astrosofista

WHY would you do that?
We need to point posters to generic solutions.
Not reinvent the wheel each time.
:-(

I think you answered your question yourself, when you wrote “There can be more than one solution to a problem, certainly.”

It isn’t my purpose to reinvet the wheel each time. I take it as a kind of mental gymnastics — you know, Alzheimer is waiting us :( —, always with the aim of helping people and learning new ways of doing things at the same time.

And sometimes I get both.

guy038

Hello, @vamshi-reddy-0, @alan-kilborn, @astrosofista and All,

As you chat with me, asking for some help, once again, @vamshi-reddy-0, I’m going to describe this generic regex !

Let FR (Find Regex ) be the regex which defines the char, string or expression to be searched
Let RR (Replacement Regex ) be the regex which defines the char, string or expression which must replace the FR expression
Let BSR ( Begin Search-region Regex ) be the regex which defines the beginning of the area where the search for FR, must start
Let ESR ( End Search-region Regex) be the regex which defines, implicitly, the area where the search for FR, must end

I assume some important hypotheses :

The Wrap around option must be ticked
The caret must be moved at the very beginning of your file, ( case of a click on the Find Next, Count, Replace or Reaplce All button )

Then, the generic regex, below, searches for any occurrence of FR, in the multi-lines range, between the BSR and ESR boundaries and replaces each occurrence of FR with RR :

SEARCH (?-i:BSR|(?!\A)\G)(?s:(?!ESR).)*?\K(?-i:FR)

REPLACE RR

But, of course, we must interpret, according to your context !

In your last post, you show us this regex :

(?-i:INSERT INTO|(?!\A)\G)(?s:(?!VALUES).)*?\K(?-i:\\[)

Seemingly, you want to delete any [ or ] chars, found in lines containing the INSERT INTO string till the end of current line, as the next one is a forbidden line, containing the string VALUES

In this specific case, you do not need at all the negative look-ahead structure (?!VALUES). Indeed, we just have to suppose that the range of chars from BSR till a [ or ] char, are standard characters, different from \r and \n. Thus, implicitly, the end of that range comes right before the line-endings chars of current line !

So your regex can be rewritten as :

(?-i:INSERT INTO|(?!\A)\G)(?-s:.)*?\K(?-i:\\[)

But as we don’t need a group in (?-s:.)*?, which can be expressed as .*? and we can report the (?-s) modifier along with the (?-i) modifier, at beginning of the regex, giving :

(?-si:INSERT INTO|(?!\A)\G).*?\K(?-i:\\[)

Now, the -i modifier in (?-i:\\[) is useless as a [ char is not a letter, and, moreover, you forgot to add the ] char. So the final regex S/R can be expressed as :

SEARCH (?-si:INSERT\x20INTO|(?!\A)\G).*?\K(?:\\[|\\])

REPLACE Leave EMPTY

With you initial sample :

INSERT INTO [emp1] ([id], [name], [age], [address])
VALUES (1, ‘john’, 23, ‘123 ad[ams street’) ;
INSERT INTO [emp2] ([id], [name], [age], [address])
VALUES (2, ‘jim’, 21, ‘123 dow]ning street’) ;
INSERT INTO [emp3] ([id], [name], [age], [address])
VALUES (3, ‘jack’, 20, ‘123 pa][rk street’) ;

we get the expected text :

INSERT INTO emp1 (id, name, age, address)
VALUES (1, ‘john’, 23, ‘123 ad[ams street’) ;
INSERT INTO emp2 (id, name, age, address)
VALUES (2, ‘jim’, 21, ‘123 dow]ning street’) ;
INSERT INTO emp3 (id, name, age, address)
VALUES (3, ‘jack’, 20, ‘123 pa][rk street’) ;

Now, if your NSERT INTO line may continue on several lines, before a VALUES line, which, itself, may be splitted in several lines, the solution is very close to the generic regex and to your regex, either !

SEARCH (?-i:INSERT\x20INTO|(?!\A)\G)(?s:(?!VALUES).)*?\K(?:\\[|\\])

REPLACE Leave EMPTY

Then, from this text :

INSERT INTO [emp1]
 ([id], [name],
 [age], [address])
VALUES (1, ‘john’, 23, ‘123 ad[ams street’) ;
INSERT INTO [emp2] ([id],
 [name], [age],
 [address])
VALUES (2, ‘jim’,
 21, ‘123 dow]ning
 street’) ;
INSERT INTO
 [emp3] ([id], [name], [age], [address])VALUES (3, ‘jack’, 20, ‘123 pa][rk street’) ;

We get this correct output text :

INSERT INTO emp1
 (id, name,
 age, address)
VALUES (1, ‘john’, 23, ‘123 ad[ams street’) ;
INSERT INTO emp2 (id,
 name, age,
 address)
VALUES (2, ‘jim’,
 21, ‘123 dow]ning
 street’) ;
INSERT INTO
 emp3 (id, name, age, address)VALUES (3, ‘jack’, 20, ‘123 pa][rk street’) ;

If some issues occur on large SQL files, this means that :

Your samples do not clearly represent your real data

and/or

The search regex is not restrictive enough, in order to avoid some cases !

Best Regards,

guy038

guy038

Hi, @vamshi-reddy-0 and All,

Due some Markdown syntax problems, when typing the square braket characters, most of the regexes, in my previous post, could not work :-(.

Indeed, as meta-characters, the [ and ] must be escaped with the \ symbol to search them as literal symbols. But the \\[ syntax has also a special meaning, with the NodeBB Markdown language !

Fortunately, as administrator, I was able to correct them. Thus, any test with the new regexes, above, should work nicely !

BR

guy038