Replace all occurrences of a character between 2 words
-
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[
. -
@Alan-Kilborn said in Replace all occurrences of a character between 2 words:
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
-
@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…
-
@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]
-
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
-
@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.
-
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! :-)
-
@Alan-Kilborn said in Replace all occurrences of a character between 2 words:
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 🙏
-
@Alan-Kilborn said in Replace all occurrences of a character between 2 words:
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.
-
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
orReaplce 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 theINSERT INTO
string till the end of current line, as the next one is a forbidden line, containing the stringVALUES
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 aVALUES
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
-
-
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 NodeBBMarkdown
language !Fortunately, as administrator, I was able to correct them. Thus, any test with the new regexes, above, should work nicely !
BR
guy038
-
Hello,@vamshi-reddy-0
Please try this code, To Replace all occurrences of a character between 2 words.
RegEx:/(?:\G(?!^)|string1)(?:(?!string1|string2).)*?\K\h(?=.*string2)/gm
I hope this RegEx will be useful to you.
Thank you. -
@guy038 said in Replace all occurrences of a character between 2 words:
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 !
Given these problems (that we usually initially forget when posting), I may consider going with this substitution instead, for any future regexes I post containing the dreaded backslash-bracket situation:
for backslash-open-square-bracket:
\x5B
for backslash-close-square-bracket:\x5D
Or…maybe not. :-)