Community
    • Login

    Replace all occurrences of a character between 2 words

    Scheduled Pinned Locked Moved Help wanted · · · – – – · · ·
    22 Posts 5 Posters 2.1k Views
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • vamshi reddy 0V
      vamshi reddy 0
      last edited by 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 KilbornA astrosofistaA 2 Replies Last reply Reply Quote 1
      • Alan KilbornA
        Alan Kilborn @vamshi reddy 0
        last edited by

        @vamshi-reddy-0

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

        1 Reply Last reply Reply Quote 0
        • vamshi reddy 0V
          vamshi reddy 0
          last edited by

          Thanks Alan, going to try that now.

          Alan KilbornA 1 Reply Last reply Reply Quote 2
          • Alan KilbornA
            Alan Kilborn @vamshi reddy 0
            last edited by

            @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.

            vamshi reddy 0V 1 Reply Last reply Reply Quote 0
            • astrosofistaA
              astrosofista @vamshi reddy 0
              last edited by 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!

              Alan KilbornA vamshi reddy 0V 2 Replies Last reply Reply Quote 1
              • vamshi reddy 0V
                vamshi reddy 0 @Alan Kilborn
                last edited by

                @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:])

                1 Reply Last reply Reply Quote 0
                • Alan KilbornA
                  Alan Kilborn @astrosofista
                  last edited by

                  @astrosofista

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

                  vamshi reddy 0V astrosofistaA 2 Replies Last reply Reply Quote 0
                  • vamshi reddy 0V
                    vamshi reddy 0 @astrosofista
                    last edited by

                    @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 :)

                    1 Reply Last reply Reply Quote 1
                    • vamshi reddy 0V
                      vamshi reddy 0 @Alan Kilborn
                      last edited by

                      @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 KilbornA 1 Reply Last reply Reply Quote 0
                      • Alan KilbornA
                        Alan Kilborn @vamshi reddy 0
                        last edited by

                        @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 0V 1 Reply Last reply Reply Quote 1
                        • vamshi reddy 0V
                          vamshi reddy 0 @Alan Kilborn
                          last edited by

                          @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 KilbornA 1 Reply Last reply Reply Quote 0
                          • Alan KilbornA
                            Alan Kilborn @vamshi reddy 0
                            last edited by

                            @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 0V 1 Reply Last reply Reply Quote 0
                            • vamshi reddy 0V
                              vamshi reddy 0 @Alan Kilborn
                              last edited by 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 KilbornA 1 Reply Last reply Reply Quote 0
                              • Alan KilbornA
                                Alan Kilborn @vamshi reddy 0
                                last edited by 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 0V 1 Reply Last reply Reply Quote 2
                                • vamshi reddy 0V
                                  vamshi reddy 0 @Alan Kilborn
                                  last edited by

                                  @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 KilbornA 1 Reply Last reply Reply Quote 2
                                  • Alan KilbornA
                                    Alan Kilborn @vamshi reddy 0
                                    last edited by

                                    @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 0V 1 Reply Last reply Reply Quote 1
                                    • vamshi reddy 0V
                                      vamshi reddy 0 @Alan Kilborn
                                      last edited by

                                      @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 🙏

                                      1 Reply Last reply Reply Quote 1
                                      • astrosofistaA
                                        astrosofista @Alan Kilborn
                                        last edited by

                                        @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.

                                        1 Reply Last reply Reply Quote 2
                                        • guy038G
                                          guy038
                                          last edited by 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

                                          1 Reply Last reply Reply Quote 2
                                          • guy038G
                                            guy038
                                            last edited by 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

                                            Alan KilbornA 1 Reply Last reply Reply Quote 0
                                            • First post
                                              Last post
                                            The Community of users of the Notepad++ text editor.
                                            Powered by NodeBB | Contributors