Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2005
    Posts
    2

    Unanswered: Extract Date From String

    Hi Gurus,

    I would like to extract only valid date such as '09/29/2008', '9/29/08' etc
    from a input string.

    eg: sample test done on 09/29/2008 abcd (need to pull 09/29/2008)
    test done by xxx on9/29/09 and the test fail - (need to pull 9/29/09)
    sample date09/29/2008 and the test pass - (need to pull 09/29/2008)


    Kindly let me know how we can achieve this.

    Regards,
    Anbu

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    In Oracle characters between single quote marks 'are strings 2008-0404 not dates'

    What is the formal data type you are operating against?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2005
    Posts
    2
    Thanks for your reply.

    The source string (varchar2) and I need to pull only date from that. Target column is also varchar2

    Best regards,
    Anbu

  4. #4
    Join Date
    Jun 2006
    Posts
    6
    If the format masks in the convert function in your test specifices that the month will always be 2 character, then it needs to always have 2 characters. If you want to pull dates (test = true) even if the month column is 1 character, then you'll need to parse it out, test the length of it, and add a leading 0 if the length is only 1.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Not a trivial task, writing a parser!

    Here's something that might get you started:
    Code:
    SQL> select text
      2  ,      translate(upper(text),'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
                                    ,'XXXXXXXXXXXXXXXXXXXXXXXXXX9999999999') mask
      3  from   t1;
    
    TEXT
    ----------------------------------------------
    MASK
    ----------------------------------------------
    eg: sample test done on 09/29/2008 abcd
    XX: XXXXXX XXXX XXXX XX 99/99/9999 XXXX
    
    test done by xxx on9/29/09 and the test fail
    XXXX XXXX XX XXX XX9/99/99 XXX XXX XXXX XXXX
    
    sample date09/29/2008 and the test pass
    XXXXXX XXXX99/99/9999 XXX XXX XXXX XXXX
    
    test 1/2/3 passed on 9/29/09!
    XXXX 9/9/9 XXXXXX XX 9/99/99!
    Now all you have to do is use INSTR to find an occurence of '9/9/99' or '9/99/9999' etc. and SUBSTR it out of the original string.
    But beware my extra example at the end!

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Using regular expressions (and sample data you provided), you might try with something like this:
    Code:
    SQL> SELECT
      2    COL,
      3    REGEXP_SUBSTR(COL, '[0-9]{1,}/[0-9]{1,}/[0-9]{2,}') result
      4  FROM TEST;
    
    COL                                                RESULT
    -------------------------------------------------- ----------
    sample test done on 09/29/2008 abcd                09/29/2008
    test done by xxx on9/29/09 and the test fail       9/29/09
    sample date09/29/2008 and the test pass            09/29/2008
    
    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •