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

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Mon, 09 Feb 2009 19:17:51 +0100
Message-ID: <4990734F.7060206_at_gmail.com>

admin_at_rbtron.com schrieb:
> Hi,
>
> I have the following details in a file:
>
> (ADDRESS =
> (CITY_DETAILS = (STREET# = 416)
> (STREET_NAME =
> UNITY BLVD.)
> (CITY = AUSTIN))
> (STATE_DETAILS = (STATE_NAME =
> TEXAS)(ZIPCODE = 12345)))
>
> I need to pull word after the word STATE_NAME. In this case it is
> TEXAS. The required word will be enclosed within '=' and ')'. These
> are multiple line rows.
>
> How to do this using regular expressions in Oracle 10g please ?
>
> Thanx
>
> -GC

Somewhat like this ?

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)
  10 select
  11 regexp_replace(s,'.*STATE_NAME\s*=\s*(\w+).*','\1',1,1,'mn') rr   12 from t
  13 ;

RR

---

TEXAS Best regards

Maxim Received on Mon Feb 09 2009 - 12:17:51 CST