This discussion is archived
3 Replies Latest reply: Nov 21, 2012 3:23 PM by Solomon Yakobson RSS

Replace date tokens in string

VANJ Journeyer
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points