Re: Find the next word using regular expressions in Oracle 10g

From: <admin_at_rbtron.com>
Date: Mon, 9 Feb 2009 15:42:33 -0800 (PST)
Message-ID: <24822274-a37f-4db4-89cb-60fbd575854f_at_m40g2000yqh.googlegroups.com>



On Feb 9, 4:27 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> ad..._at_rbtron.com schrieb:
>
>
>
>
>
> > On Feb 9, 3:35 pm, ad..._at_rbtron.com wrote:
> >> Awesome answers! ... Thanks a lot ... just one more thing:
>
> >> Sometimes the file will have :
>
> >> (STATE_DETAILS = (STATE_NAME =
> >>                                       TEXAS)(ZIPCODE = 12345))
>
> >> or
>
> >> (STATE_DETAILS = (TERRITORY =
> >>                                       TEXAS)(ZIPCODE = 12345))
>
> >> So how to find the word after : either STATE_NAME or TERRITORY ?
>
> >> Thanx once again!
>
> >> -GC
>
> > Ok, I got that boolean part ... I modified it to [STATE_NAME |
> > TERRITORY] and it worked fine ...
>
> > Two other things:
>
> > 1) Some files have Territory as US.TEXAS ... in such cases, the script
> > outputs only US not US.TEXAS
>
> > 2) Some files don't have STATE_NAME or TERRITORY in them. I need to
> > output the word NULL in such cases.
>
> > Thanks!
>
> SQL> set NULL NULL
> SQL> with t as (
>    2  select '(ADDRESS =
>    3          (CITY_DETAILS =    (STREET# = 416)
>    4                        (STREET_NAME =
>    5                        UNITY BLVD.)
>    6                        (CITY = AUSTIN))
>    7          (STATE_DETAILS = (STATE_NAME =
>    8                                        TEXAS)(ZIPCODE = 12345)))' s
>    9  from dual union all
>   10  select '(ADDRESS =
>   11          (CITY_DETAILS =    (STREET# = 416)
>   12                        (STREET_NAME =
>   13                        UNITY BLVD.)
>   14                        (CITY = AUSTIN))
>   15          (STATE_DETAILS = (TERRITORY =
>   16                                        US.TEXAS)(ZIPCODE = 12345)))' s
>   17  from dual union all
>   18  select '(ADDRESS =
>   19          (CITY_DETAILS =    (STREET# = 416)
>   20                        (STREET_NAME =
>   21                        UNITY BLVD.)
>   22                        (CITY = AUSTIN))
>   23          (STATE_DETAILS = (STATE_NAM =
>   24                                        TEXAS)(ZIPCODE = 12345)))' s
>   25  from dual
>   26  )
>   27  select
>   28  nullif(
>   29      regexp_replace(s,
>   30          '.*(STATE_NAME|TERRITORY)\s*=\s*((\w|\.)+).*',
>   31          '\2',
>   32          1,
>   33          1,
>   34          'mn'),s
>   35      ) rr
>   36  from t
>   37  ;
>
> RR
> ---------------------------------------------------------------------------­--------------------------------------------------
> TEXAS
> US.TEXAS
> NULL
>
> If your client is not sqlplus, wrap whole expression into nvl
>
> Best regards
>
> Maxim- Hide quoted text -
>
> - Show quoted text -

Awesome, again ... thanks !

4 words : You are the best !!! Received on Mon Feb 09 2009 - 17:42:33 CST

Original text of this message