This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Oct 23, 2013 12:02 AM by ranit B Go to original post RSS
  • 15. Re: Reg: REGEXP_REPLACE issue -
    ranit B Expert
    Currently Being Moderated

    Hi Etbin,

     

    I know requirement is very strange and it is waste to break our heads on this. Can't help myself... and only way is to rectify the data model.

  • 16. Re: Reg: REGEXP_REPLACE issue -
    ranit B Expert
    Currently Being Moderated

    Sorry Blu, forgot to mention this -

    It is MONTH which always comes first in the pattern here and then followed by DATE and then YEAR.

     

    I know this is a stupid requirement, but can't even help myself. Just trying to fix this.

     

    Actually, it is a "Comments" column and data already entered is in this format. We can introduce new data model (with different fields) but the existing data needs to be extracted from this existing column.

     

    Any pointers?

  • 17. Re: Reg: REGEXP_REPLACE issue -
    Etbin Guru
    Currently Being Moderated
    Actually, it is a "Comments" column and data already entered is in this format.


    I thought it was development related but I see it now. (the omelette is ready - to be read as - the eggs are broken already)


    Regards


    Etbin

  • 18. Re: Reg: REGEXP_REPLACE issue -
    ranit B Expert
    Currently Being Moderated

    - what purpose does the date part serve?

    It is the Date on which users have made some transaction. Might be they inserted long back as comment, but now realizing its importance.

     

    What should happen if a user accidentally enters a date that is an invalid date (for example: 01-0-20013)  and you run into it when extracting?

    Then that should not be fetched, instead keep NULL.

     

    No, you'll never get it 100% nailed shut I'm afraid. Dates simply aren't strings. Extracting dates from strings is a cumbersome process.

    Best would be to introduce a separate COMMENT_DATE (or whatever name suits) column in the application, that way you'll be much much better off...

    Yes Hoek, I can understand your points... and they are very true.

     

    Let's see, I guess have to propose a new data model to rectify this.

     

    -- Ranit

  • 19. Re: Reg: REGEXP_REPLACE issue -
    Etbin Guru
    Currently Being Moderated
    It is MONTH which always comes first in the pattern here and then followed by DATE and then YEAR.


    There might be some hope (almost certainly not for a complete solution, but you might come rather close)

    Speaking roughly you're after something like (in pseudo code of course)

    [01]?[0..9]|[JANURFEBMCHPILYNGSTOVD]{3,9}~[0123]?[0..9]~[0..9]{2,4} where ~ stands for the separator

    ((zero or one or none and another digit) or string_of_letters) followed by ...

    after replacing all the rest (not mentioned above) in the upper(looking_like_date) with a single separator and trimming the leading and the trailing one if present.


    Regards


    Etbin

  • 20. Re: Reg: REGEXP_REPLACE issue -
    Etbin Guru
    Currently Being Moderated

    I decided I have to dedicate some time to regular expressions (I must use them in rare occasions only, so I'm definitely not good at)

     

    with

    comment_columns as

    (select 'before Sep; 10 / 2013 it did not exist' cmnt from dual union all

    select 'not available before April 30./2013' from dual union all

    select 'after 6/30/2013 call Augusta between 10 and 14 only' from dual union all

    select 'She may not leave before 10:30 tomorrow' from dual union all

    select '10/20/30 is his birthday' from dual union all

    select 'Frank Russell Capra born May 18, 1897 ' from dual union all

    select 'the value was (@ 10:30) 11.2233 - free market price' from dual union all

    select 'free market price currently @ 10.98765' from dual union all

    select 'pay back $500 as promised on Friday July 4 2010' from dual union all

    select 'only 5 minutes after midnight 11/12/13' from dual union all

    select 'after October; 31. 99 11 similar events occurred' from dual

    ),

    to_notice as

    (select cmnt source,

            regexp_substr(upper(cmnt),

                          '\s((JAN|JANUARY)|(FEB|FEBRUARY)|(MAR|MARCH)|(APR|APRIL)|MAY|(JUN|JUNE)|(JUL|JULY)|' ||

                          '(AUG|AUGUST)|(SEP|SEPTRMBER)|(OCT|OCTOBER)|(NOV|NOVEMBER)|(DEC|DECEMBER))' ||

                          '[ ;:,.]*[0123]?\d[ .,-/]*?(\d{2})?(\d{2})'

                         ) noticed_mon,

            regexp_substr(upper(cmnt),

                          '[01]?\d[ .-/]*?[0123]?\d[ .-/]*?(\d{2})?(\d{2})'

                         ) noticed_num

       from comment_columns

    ),

    to_reformat as

    (select source,

            regexp_replace(noticed_mon,

                           '(\w{3})\D*(\d?\d)[ .,-/]*((\d{2})?(\d{2}))$',

                           '\1~\2~\3'

                          ) reformatted_nam,

            rtrim(regexp_replace(regexp_replace(noticed_num,'\D','~'),'~+','~'),'~') reformatted_num

       from to_notice

    )

    select source,

           reformatted_nam || case when regexp_count(reformatted_num,'~') = 2 then reformatted_num end the_date

      from to_reformat

     

    SOURCETHE_DATE
    before Sep; 10 / 2013 it did not existSEP~10~2013
    not available before April 30./2013APR~30~2013
    after 6/30/2013 call Augusta between 10 and 14 only6~30~2013
    She may not leave before 10:30 tomorrow-
    10/20/30 is his birthday10~20~30
    Frank Russell Capra born May 18, 1897MAY~18~1897
    the value was (@ 10:30) 11.2233 - free market price-
    free market price currently @ 10.98765-
    pay back $500 as promised on Friday July 4 2010JUL~4~2010
    only 5 minutes after midnight 11/12/1311~12~13
    after October; 31. 99 11 similar events occurredOCT~31~99

     

    Regards

     

    Etbin

  • 21. Re: Reg: REGEXP_REPLACE issue -
    ranit B Expert
    Currently Being Moderated

    Sorry for the late reply, Etbin.

     

    I'm also poor in Regexps... but want to learn it (very useful for complex checks and validations).

     

    This query is just so wonderful. Really appreciate your efforts, dedication and intelligence. You inspire juniors like us. Thanks!!!


1 2 Previous Next

Legend

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