1 2 Previous Next 16 Replies Latest reply: Apr 3, 2009 6:25 AM by 800282 RSS

    Regex for String replacement needed

    843789
      Hi,

      my problem:
      For example I have a String like "ABC DEF 'ABC' ABCD ". Now I want to replace all "ABC" within the String, which stand alone and aren't quoted, with "'ABC'".
      That means, after the replacement I want a String like this "'ABC' DEF 'ABC' ABCD".

      In my case I have to escape all ORACLE-TEXT operators in a String. That means I have to replace "AND", "OR", "WITHIN", ...

      Thanks
      Jonny
        • 1. Re: Regex for String replacement needed
          843789
          Use a prepared statement then you won't need to quote anything.
          • 2. Re: Regex for String replacement needed
            843789
            The problem is, I don't want to replace all the operators. Some operators are allowed.

            Cheers
            Jonny
            • 3. Re: Regex for String replacement needed
              843789
              jonnybecker wrote:
              The problem is, I don't want to replace all the operators. Some operators are allowed.
              Are you saying you can't use PreparedStatement? If so then why?
              • 4. Re: Regex for String replacement needed
                843789
                Are you saying you can't use PreparedStatement?
                Yes
                If so then why?
                This String is used within a Hibernate Criteria Query. To make it even more difficult the contains method is implemented in native SQL.
                Criterion criterion = Restrictions.sqlRestriction(stringNativeSql, searchTermOracletext, Hibernate.STRING)
                .

                That's my problem.

                Cheers
                Jonny
                • 5. Re: Regex for String replacement needed
                  843789
                  jonnybecker wrote:
                  If so then why?
                  This String is used within a Hibernate Criteria Query. To make it even more difficult the contains method is implemented in native SQL.
                  Criterion criterion = Restrictions.sqlRestriction(stringNativeSql, searchTermOracletext, Hibernate.STRING)
                  .
                  I would expect Hibernate to have a means of dealing with this because using a regex is likely to be fragile. You could ask on a Hibernate forum.
                  • 6. Re: Regex for String replacement needed
                    843789
                    My problem is that I have a running system and my my is to modify this searchterm, which is being modified multiple times before. So I don't wanna mess around to much.

                    @sabre150: I highly appreciate your generous help and suggestions. But for my/this special case, they are unfortunately not the answer for my problem.

                    Cheers
                    Jonny

                    Edited by: jonnybecker on Apr 2, 2009 3:26 PM
                    • 7. Re: Regex for String replacement needed
                      800282
                      jonnybecker wrote:
                      My problem is that I have a running system and my my is to modify this searchterm, which is being modified multiple times before. So I don't wanna mess around to much.
                      This is like fixing your roof with Duct tape: it may solve your problem for a short while, but it's bound to result in leaks in the near future.
                      jonnybecker wrote:
                      @sabre150: I highly appreciate your generous help and suggestions. But for my/this special case, they are unfortunately not the answer for my problem.
                      Here's a decent regex tutorial: [http://www.regular-expressions.info/tutorial.html]
                      Feel free to post back if you run into problems implementing your solution.

                      Good luck!
                      • 8. Re: Regex for String replacement needed
                        843789
                        This is like fixing your roof with Duct tape: it may solve your problem for a short while, but it's bound to result in leaks in the near future.
                        You sure are right about that!
                        Here's a decent regex tutorial: http://www.regular-expressions.info/tutorial.html
                        I'll have a look into this. Thank you.

                        Cheers
                        Jonny
                        • 9. Re: Regex for String replacement needed
                          843789
                          And here the solution for my problem (assuming I'm only escaping "ABOUT" and "ACCUM"):
                          Pattern p = Pattern.compile("([^']|^)\\b(ABOUT|ACCUM)\\b([^']|$)");
                          Matcher m = p.matcher(searchTerm);
                          String string = m.replaceAll("$1'$2'$3");
                          Cheers
                          Jonny
                          • 10. Re: Regex for String replacement needed
                            800282
                            jonnybecker wrote:
                            And here the solution for my problem (assuming I'm only escaping "ABOUT" and "ACCUM"):
                            Pattern p = Pattern.compile("([^']|^)\\b(ABOUT|ACCUM)\\b([^']|$)");
                            Matcher m = p.matcher(searchTerm);
                            String string = m.replaceAll("$1'$2'$3");
                            Cheers
                            Jonny
                            Well done.
                            A slightly shorter version would be to use look-arounds and to String's replaceAll(...) method:
                            String s= searchTerm.replaceAll("\\b(?<!')(ABOUT|ACCUM)(?!')\\b", "'$1'");
                            • 11. Re: Regex for String replacement needed
                              843789
                              I do hope that ABOUT and ACCUM never appear except as key words because there is nothing in either regex to deal with something like

                              'abc ABOUT def ACCUM ghi'

                              Or am I missing the point?
                              • 12. Re: Regex for String replacement needed
                                800282
                                sabre150 wrote:
                                I do hope that ABOUT and ACCUM never appear except as key words because there is nothing in either regex to deal with something like

                                'abc ABOUT def ACCUM ghi'

                                Or am I missing the point?
                                Nah, but the OP has been warned more than once now about the risk of using regex for this.

                                Edited by: prometheuzz on 3-apr-2009 12:21

                                But okay, here's a slightly less error prone Duct tape solution:
                                String s = "aaa ACCUM AABOUT ABOUTB 'abc ABOUT def ACCUM ghi' ABOUT 'ABOUT \\\\\\' xyz \\' ACCUM abc' aaa ACCUM bbb ACCUM";
                                System.out.println(s);
                                System.out.println(s.replaceAll("(?s)\\b(ACCUM|ABOUT)\\b(?=(?:[^\\\\']*'(?:\\\\.|[^'])*')*[^\\\\']*$)", "'$1'"));
                                • 13. Re: Regex for String replacement needed
                                  843789
                                  Ok - I'm not going to try to read the regex since I hate trying to understand my own regex just a couple of hours after writing them. The question is - does it now handle quoted quotes within quotes?

                                  Jos says that the 'pumping lemma' means that one cannot normally use regex for this sort of task. Since I don't have the background or the interest I can't really comment on whether or not the 'pumping lemma' applies to this grammar but I do know that using regex to try to quote SQL is difficult and it is usually best to use a parser.
                                  • 14. Re: Regex for String replacement needed
                                    800282
                                    sabre150 wrote:
                                    Ok - I'm not going to try to read the regex since I hate trying to understand my own regex just a couple of hours after writing them. The question is - does it now handle quoted quotes within quotes?
                                    I forgot that SQL quotes it's quotes. My regex uses a backslash to quote them. But that is easily fixed:
                                    String s = "aaa ACCUM AABOUT ABOUTB 'abc ABOUT def ACCUM ghi' ABOUT 'ABOUT '' xyz '' ACCUM abc' aaa ACCUM bbb ACCUM";
                                    System.out.println(s);
                                    System.out.println(s.replaceAll("(?s)\\b(ACCUM|ABOUT)\\b(?=(?:[^']*'(?:''|[^'])+')*[^']*$)", "'$1'"));
                                    Jos says that the 'pumping lemma' means that one cannot normally use regex for this sort of task. Since I don't have the background or the interest I can't really comment on whether or not the 'pumping lemma' applies to this grammar but I do know that using regex to try to quote SQL is difficult and it is usually best to use a parser.
                                    AFAIK, you can't use regex to match nested characters (like open- and close-parenthesis) and let the regex engine "remember" the number of open- and close-character. But in this case, when a (single) quote marks the start of a piece of quoted text, you just need to greedily match either two successive quotes, or a single character other than a quote (which is what the regex does). Since there's no recursion going on, it can be done using regex. Of course, I may well be over simplifying this part of the SQL language, but the quotes (and quoted quotes) should go okay.
                                    In BNF this would look like:
                                    QUOTED_TEXT  :  QUOTE ATOM+ QUOTE
                                                 ;
                                    
                                    ATOM         :  QUOTE QUOTE
                                                 |  ~QUOTE
                                                 ;
                                    
                                    QUOTE        :  '
                                                 ; 
                                    ==
                                    '(?:''|[^'])+'
                                    1 2 Previous Next