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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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: