Oracle Illustrated

Feb 21 2010   8:21AM GMT

Migrating from 9i to 11g – Regular expression

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

From Oracle 10g can use regular expressions – some of

1 REGEXP_SUBSTR

REGEXP_SUBSTR searches for a regular expression pattern in a given text – this returns a matching substring.
select regexp_substr(‘Employee id is : 420147321’, ‘[0-9]{9}’, 5) from dual;
REGEXP_SU
———
420147321
Elapsed: 00:00:00.03

From Oracle 11g – nth sub-expression in REGEXP_SUBSTR can be accessed.
Example to access nth sub-expression- say from the text luxvttctdmand – eliminate lux & and and return the rest.
SELECT
REGEXP_SUBSTR
(‘luxvttctdmand’, — source
‘lux(.*)and’, — regular expression pattern
1, — position (to start the search)
1, — number of occurances
‘i’, — match option (case insensitive / non case sensitive)
1) — subexpression
FROM dual;
REGEXP_
——-
vttctdm

*********************************************************************************

2 REGEXP_INSTR

REGEXP_INSTR searches for a regular expression pattern in a given text – this returns the position as output.
select regexp_instr(‘Employee id is : 420147321’, ‘[0-9]{9}’) from dual
REGEXP_INSTR(‘EMPLOYEEIDIS:420147321′,'[0-9]{9}’)
————————————————-
18
Elapsed: 00:00:00.06
From Oracle 11g – nth sub-expression in REGEXP_INSTR can be accessed.
Example to access nth sub-expression- say from the text luxvttlasfluxoiwljljljkasdfctdmand.
SELECT
REGEXP_INSTR
(‘luxvttlasfluxoiwljljljkasdfctdmand’,
‘(lux (vtt) (and))’,
1,
1, 0,
‘i’,
1) inst
FROM dual;

*********************************************************************************

3 REGEXP_COUNT

REGEXP_COUNT is like normal count function – but accepts regular expression characters.
select regexp_count(‘Employee id is : 420147321’, ‘[0-9]{9}’) from dual
REGEXP_COUNT(‘EMPLOYEEIDIS:420147321′,'[0-9]{9}’)
————————————————-
1
Elapsed: 00:00:00.09
From Oracle 11g it returns the number of times a pattern appears in a string-
Example-
select regexp_count
(‘luxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxand’, ‘lux’) cnt from dual
CNT
———-
24

*********************************************************************************

4 REGEXP_LIKE

This function is similar to like but it does a regular expression matching. It can be used in the where clause and can be used in PL/SQL IF condition.

Example –
select 1 from dual where regexp_like(‘Employee id is : 420 CDF 321’, ‘[0-9]{3} [A-Z]{3} [0-9]{3}’)

1
———-
1

Elapsed: 00:00:00.07

*********************************************************************************

5 REGEXP_REPLACE
REGEXP_REPLACE enables to search the string based on patters and display the results.
Example 1 – Continuous text
select regexp_replace(‘Employee id is : 420147321’, ‘[0-9]{9}’, ‘*********’) from dual

REGEXP_REPLACE(‘EMPLOYEEID
————————–
Employee id is : *********

Elapsed: 00:00:00.36

Example 2 – Text with space

select regexp_replace(‘Employee id is : 420 CDF 321’, ‘[0-9]{3} [A-Z]{3} [0-9]{3}’, ‘*** *** ***’) from dual;

REGEXP_REPLACE(‘EMPLOYEEIDIS
—————————-
Employee id is : *** *** ***

Elapsed: 00:00:00.03

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: