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 -
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