4 Replies Latest reply on Jul 6, 2011 12:36 PM by Frank Kulash

    regexp_replace

    user5116754
      How to replace all this German letters like (ü,ö,ä...)


      select regexp_replace('Jürgen könnte übermorgen den Text ändern.','[ÖÜÄöüäß]', '(oeaeuess') NEW_TEXT from dual;

      Of course this doesn't work...

      All suggestions are welcome.
        • 1. Re: regexp_replace
          Saubhik
          Like this ? REGEXP_REPLACE(str,'[^[a-z,A-Z]]*',' ')
          • 2. Re: regexp_replace
            Frank Kulash
            Hi,
            user5116754 wrote:
            How to replace all this German letters like (ü,ö,ä...)


            select regexp_replace('Jürgen könnte übermorgen den Text ändern.','[ÖÜÄöüäß]', '(oeaeuess') NEW_TEXT from dual;

            Of course this doesn't work...

            All suggestions are welcome.
            I think you'll need nested operations to do that. For example:
            SELECT      TRANSLATE ( REGEXP_REPLACE ( REPLACE ( 'Jürgen könnte übermorgen den Text ändern.'
                                          , 'ß'
                                          , 'ss'
                                          )
                                   , '([ÖÜÄöüä])'
                                   , '\1e'
                                   )
                        , 'ÖÜÄöüä'
                        , 'OUAoua'
                        )      AS new_text 
            FROM     dual;
            • 3. Re: regexp_replace
              user5116754
              Yesssss, it works. Thank you Frank.
              ...but a simple regexp_replace - statement can't achieve the same???
              • 4. Re: regexp_replace
                Frank Kulash
                user5116754 wrote:
                Yesssss, it works. Thank you Frank.
                ...but a simple regexp_replace - statement can't achieve the same???
                Sorry, I don't think so. Maybe you could use a single statement to replace all the umlauts, since there is a common pattern to all of them, but the ess-zed would still need a separate operation.
                You really want something like TRANSLATE that works on strings, not single characters, but, unfortunately, that doesn't exist. See the following thread for another work-around:
                SQL Query

                The solution I posted always replace the umlaut with a lower-case 'e'. If you want to detect all-upper-case words, and change 'ÜBER' into 'UEBER' (rather than 'UeBER'), then use PL/SQL.