3 Replies Latest reply: Nov 21, 2012 5:23 PM by Solomon Yakobson RSS

    Replace date tokens in string

    VANJ
      Given a string like
      foo#yyyymmdd#.txt
      how can regexp_replace be used to replace the #...# token with some date (e.g. sysdate) in that format? Tried
      regexp_replace(?,'#([^#])+#',to_char(sysdate,'\1'))
      but the replacement string for regexp_replace has to be a string, not a function.

      Or maybe forget about regexp_replace and wrap the rest of the string in double-quotes *"foo"yyyymmdd".txt"* and simply pass that as a date format to to_char() and Oracle will leave the quoted part alone.

      Thoughts? Thanks
        • 1. Re: Replace date tokens in string
          Frank Kulash
          Hi,
          VANJ wrote:
          Given a string like
          foo#yyyymmdd#.txt
          how can regexp_replace be used to replace the #...# token with some date (e.g. sysdate) in that format? Tried
          regexp_replace(?,'#([^#])+#',to_char(sysdate,'\1'))
          but the replacement string for regexp_replace has to be a string, not a function.
          Yes, the argument has to be a string, but the function returns a string. The error above is that the 2nd argument to TO_CHAR is '\1'.
          Or maybe forget about regexp_replace and wrap the rest of the string in double-quotes *"foo"yyyymmdd".txt"* and simply pass that as a date format to to_char() and Oracle will leave the quoted part alone.

          Thoughts? Thanks
          If you know the format:
          REGEXP_REPLACE ( str
                      , '#[^#]*#'
                      , TO_CHAR (SYSDATE, 'YYYYMMDD')
                      )
          If the format is between the # signs:
          REGEXP_REPLACE ( str
                      , '#[^#]+#'
                      , TO_CHAR ( SYSDATE
                                  , TRIM ( BOTH '#' FROM 
                                   REGEXP_SUBSTR ( str
                                          , '#[^#]+#'
                                        )
                              )
                          )
                      )
          Notice that, in either case, the 3rd argument to REGEXP_REPLACE is the string returned by a function.

          Edited by: Frank Kulash on Nov 21, 2012 3:14 PM


           

          I hope this answers your question.
          If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
          Explain, using specific examples, how you get those results from that data.
          Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
          See the forum FAQ {message:id=9360002}
          • 2. Re: Replace date tokens in string
            ranit B
            Please check this...
            with xx as (
              select 'foo#yyyymmdd#.txt' txt from dual
            )
            select regexp_replace(txt,'#[^#]+#',to_char(sysdate,'yyyymmdd')) from xx;
            gives
            o/p = foo20121122.txt
            • 3. Re: Replace date tokens in string
              Solomon Yakobson
              ranit B wrote:
              Please check this...
              And what if string is
              'foo#mmddyyyy#.txt'
              What OP wanted was to use pattern within search string as format mask. Unfortunately this is not possible in Oracle regexp. Frank provided a workaround which will work properly only if string has a single occurence of '#[^#]+#' or all occurences are the same. For example, it will work for:
              SQL> with t as (
                2             select 'foo#yyyymmdd#bar#yyyymmdd#.txt' str from dual
                3            )
                4  select  regexp_replace(
                5                         str,
                6                         '#[^#]+#',
                7                         to_char(
                8                                 sysdate,
                9                                 trim(
               10                                      both '#' from
               11                                      regexp_substr(
               12                                                    str,
               13                                                    '#[^#]+#'
               14                                                   )
               15                                     )
               16                                )
               17                        ) txt
               18    from  t
               19  /
              
              TXT
              --------------------------------------------------------------------------------
              
              foo20121121bar20121121.txt
              Since both occurences are yyyymmdd. But if, for example, one is yyyymmdd and other is mmddyyyy:
              SQL> with t as (
                2             select 'foo#yyyymmdd#bar#mmddyyyy#.txt' str from dual
                3            )
                4  select  regexp_replace(
                5                         str,
                6                         '#[^#]+#',
                7                         to_char(
                8                                 sysdate,
                9                                 trim(
               10                                      both '#' from
               11                                      regexp_substr(
               12                                                    str,
               13                                                    '#[^#]+#'
               14                                                   )
               15                                     )
               16                                )
               17                        ) txt
               18    from  t
               19  /
              
              TXT
              --------------------------------------------------------------------------------
              
              foo20121121bar20121121.txt
              
              SQL>
              while foo20121121bar11212012.txt would be expected.

              SY.