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

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Mon, 09 Feb 2009 23:27:50 +0100
Message-ID: <4990ADE6.3070802_at_gmail.com>

admin_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 Received on Mon Feb 09 2009 - 16:27:50 CST