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