Find the next word using regular expressions in Oracle 10g

#11

On 10 Feb, 17:50, Maxim <mdeme... (AT) gmail (DOT) com> wrote:

Quote:

On 10 Feb., 13:17, gazzag <gar... (AT) jamms (DOT) org> wrote:

On 9 Feb, 18:17, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote:

ad... (AT) rbtron (DOT) 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

Sure,
the regexp_replace(s,'.*STATE_NAME\s*=\s*(\w+).*','\1' ,1,1,'mn')
means:
match the original string as
1) .* * # anything followed by
2) STAT_NAME *# literally STAT_NAME followed by
3) \s* # zero or many whitespaces followed by
4) = # literally "=" sign followed by
5) \s* # zero or many whitespaces followed by
6) \w+ # one or many word (alphanumeric or _ ) characters followed by
7) .* anything

Part 6) is the part which was interesting for OP - any word which
occurs right after "STATE_NAME = "
To be able backreference this part in the replace expression, it
should be enclosed into brackets - so you define (up to 10) groups
within your regexp, which can be then referenced later with \1, \2, \3
and so on.
In nonformal terms it sounds: match the whole string, define group
within it which consists of a word following the "STAT_NAME = " and
replace the match (again , whole string) by first backreferenced group
(\1).
The rest of parameters means - search from the 1 position in a string
and consider only the 1st match, besides that, consider the string as
multiline string ('m' parameter) and consider the "." to match *the
newline symbols ( 'n' parameter) - and this is true in this example *-
the leading and trailing ".*" should cover newlines as well.
Without the last two parameters, search would not be continued after
the newline.

Best regards

Maxim

Maxim,

Many thanks for the excellent explanation.

Kind regards,

-g

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Find the next word using regular expressions in Oracle 10g

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Find the next word using regular expressions in Oracle 10g - 02-10-2009 , 12:13 PM