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

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Mon, 9 Feb 2009 20:19:59 +0100
Message-ID: <499081e2$0$3515$426a34cc_at_news.free.fr>

<admin_at_rbtron.com> a écrit dans le message de news: fc2e053d-8915-40f4-ab87-b905bd656a38_at_v42g2000yqj.googlegroups.com...
| 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

SQL> with
  2 data as (

  3      select '(ADDRESS =
  4          (CITY_DETAILS =    (STREET# = 416)
  5                        (STREET_NAME =
  6                        UNITY BLVD.)
  7                        (CITY = AUSTIN))
  8          (STATE_DETAILS = (STATE_NAME =
  9                                        TEXAS)(ZIPCODE = 12345)))' val
 10      from dual

 11 )
 12 select regexp_replace(replace(val,'  13 ',''), '^.*STATE_NAME[[:space:]]*=[[:space:]]*([[:alpha:]]+)[[:space:]]*\).*$','\1')  14 from data
 15 /
REGEX

---

TEXAS 1 row selected.

Regards
Michel Received on Mon Feb 09 2009 - 13:19:59 CST