From 10g in Oracle there is a significant power is given to all new Oracle Programmer and that is inherited from POSIX. Yes, you guess right - it is regular expression. One of the most powerful features that is missing for quite some time and programmer no need to write big and complex functions or procedures to produce their required result.
In this post i'm not going to discuss about the syntax and all the minute details which require to use it. Rather, i'll concentrate on some useful snippet on regular expression which might be helpful for you in your case or may require little enhancement from your part.
As i follow OTN, and find these requirements are frequently needed by many programmer. So, why not compiled them in a single platform. If you want to know the basic syntax of regular expression then you can visit this place.
One thing i would like to tell to produce the formatted output - i use CAST function here. Otherwise, it is not required in most of the case where i've used in this post. Hope you don't get confused for this.
Splitting comma-separated values:
scott@ORCL>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
Elapsed: 00:00:00.00
scott@ORCL>
scott@ORCL>
scott@ORCL>with tt
2 as
3 (
4 select 1 id, 'saty,anup,john,sas' src from dual
5 union all
6 select 2, 'shank,rajib,papu,sanjit,amit' from dual
7 )
8 select k.*
9 from (
10 select distinct id,
11 regexp_substr(src,'[^,]+',1,level) cooked_src
12 from tt
13 connect by level <= (length(src) - length(replace(src,',',''))) + 1
14 ) k
15 order by k.id,k.cooked_src;
ID COOKED_SRC
---------- ----------------------------
1 anup
1 john
1 sas
1 saty
2 amit
2 papu
2 rajib
2 sanjit
2 shank
9 rows selected.
Elapsed: 00:00:00.38
scott@ORCL>
scott@ORCL>Insert a space inside a joined word:
scott@ORCL>with res
2 as
3 (
4 select 'SatyakiDe' cola from dual
5 union all
6 select 'RajibChakroborty' from dual
7 union all
8 select 'PranabPaul' from dual
9 )
10 select cola,
11 cast(regexp_replace(cola,'^(.*)([[:upper:]]{1,})(.*)$','\1 \2\3') as varchar2(40)) cooked_cola
12 from res;
COLA COOKED_COLA
---------------- ----------------------------------------
SatyakiDe Satyaki De
RajibChakroborty Rajib Chakroborty
PranabPaul Pranab Paul
Elapsed: 00:00:00.04
scott@ORCL>Insert a space inside a joined word:
scott@ORCL>
scott@ORCL>with fer
2 as
3 (
4 select '919047242526' cola from dual
5 union all
6 select '919038220261' from dual
7 )
8 select cola,
9 cast(regexp_replace(cola,'^([[:digit:]]{2})([[:digit:]]{10})','+\1 \2') as varchar2(15)) cooked_cola
10 from fer;
COLA COOKED_COLA
------------ ---------------
919047242526 +91 9047242526
919038220261 +91 9038220261
Elapsed: 00:00:00.03
scott@ORCL>Remove one + from the source:
scott@ORCL>with hig
2 as
3 (
4 select '+++C' cola from dual
5 )
6 select cola,
7 cast(regexp_replace(cola,'^([[:punct:]]{2})([[:punct:]]{1})(.*)$','\1\3') as varchar2(5)) cooked_cola
8 from hig;
COLA COOKE
---- -----
+++C ++C
Elapsed: 00:00:00.02
scott@ORCL>Extracting number from string:
scott@ORCL>with tep 2 as 3 ( 4 select 'satyaki9de0is3a8studious2and2energetic0software2engineer6here1' cola from dual 5 ) 6 select cola, 7 cast(regexp_replace(cola,'[^0-9]','') as varchar2(12)) cooked_cola 8 from tep; COLA COOKED_COLA -------------------------------------------------------------- ------------ satyaki9de0is3a8studious2and2energetic0software2engineer6here1 9038220261 Elapsed: 00:00:00.03 scott@ORCL>
Extracting names from mails:
scott@ORCL> scott@ORCL>with reg 2 as 3 ( 4 select 'satyaki.de@gmail.com' cola from dual 5 union all 6 select 'pranab.paul@aol.in' from dual 7 union all 8 select 'tuhin.chakroborty@rediffmail.com' from dual 9 union all 10 select 'debraj.saha@yahoo.com' from dual 11 ) 12 select cola, 13 cast( 14 initcap(regexp_replace(regexp_substr(cola,'[^@]+'),'(.*)(\.)(.*)','\1 \3')) as varchar2(50) 15 ) cooked_cola 16 from reg; COLA COOKED_COLA -------------------------------- -------------------------------------------------- satyaki.de@gmail.com Satyaki De pranab.paul@aol.in Pranab Paul tuhin.chakroborty@rediffmail.com Tuhin Chakroborty debraj.saha@yahoo.com Debraj Saha Elapsed: 00:00:00.03 scott@ORCL>
Insert spaces between small & caps letter:
scott@ORCL> scott@ORCL>with kit 2 as 3 ( 4 select 'AbraCaDabra' cola from dual 5 ) 6 select cola, 7 cast( 8 regexp_replace(cola,'([[:lower:]])([[:upper:]])','\1 \2') as varchar2(20) 9 ) cooked_cola 10 from kit; COLA COOKED_COLA ----------- -------------------- AbraCaDabra Abra Ca Dabra Elapsed: 00:00:00.02 scott@ORCL>
Masking credit card information:
scott@ORCL>with jol
2 as
3 (
4 select '4567098723560984' cola from dual
5 )
6 select cola,
7 cast(
8 regexp_replace(cola,'([[:digit:]]{4})([[:digit:]]{4})([[:digit:]]{4})([[:digit:]]{4})','\1-XXXX-XXXX-\4')
9 as varchar2(20)
10 ) cooked_cola
11 from jol;
COLA COOKED_COLA
---------------- --------------------
4567098723560984 4567-XXXX-XXXX-0984
Elapsed: 00:00:00.02
scott@ORCL>Convert a number series to ip address & also print this in reverse order:
scott@ORCL>
scott@ORCL>with rev
2 as
3 (
4 select '123456789' cola from dual
5 )
6 select cola,
7 cast(
8 regexp_replace(cola,'([[:digit:]]{3})([[:digit:]]{3})([[:digit:]]{3})','\3.\2.\1')
9 as varchar2(15)
10 ) cooked_cola
11 from rev;
COLA COOKED_COLA
--------- ---------------
123456789 789.456.123
Elapsed: 00:00:00.02
scott@ORCL>Hope you like this.
Very soon i'm going to post another useful snippet on some other topic. Till then best of luck and keep following this blog.