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

From: gazzag <gareth_at_jamms.org>
Date: Tue, 10 Feb 2009 04:17:36 -0800 (PST)
Message-ID: <6f13cf13-3a75-45db-b0f1-bb7daba4fdfd_at_h5g2000yqh.googlegroups.com>

On 9 Feb, 18:17, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> ad..._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

Maxim,

Would you mind explaining how the REGEXP_REPLACE function is working in your example, please? I am familiar with regular expressions but only on a fairly basic level.

Cheers,

-g Received on Tue Feb 10 2009 - 06:17:36 CST