Square DBA – Oracle DBA Talk

REGEX in Oracle

Yesterday I used a bit of regex to cleanup a phone number.  Today I needed to take it a step further.  We use a session boarder controller made by a company called Acme.  Well their phone number in their call detail records are SIP string so I needed to parse out the phone number.  What made it fun was sometime the phone number is 7 digits adn in those cases I needed to take the area code from where the call originated.  Here is the code to accomplish this.

nvl(case when length(ltrim(substr(REGEXP_REPLACE(
PRIMARY_ROUTING_NUMBER,
'[+]',NULL),instr(REGEXP_REPLACE(PRIMARY_ROUTING_NUMBER,
'[+]',NULL),'sip:')+4,
instr(REGEXP_REPLACE(substr(REGEXP_REPLACE(
PRIMARY_ROUTING_NUMBER,
'[+]',NULL),instr(REGEXP_REPLACE(
PRIMARY_ROUTING_NUMBER,'[+]',NULL),'sip:')+4,100)
,'[^[:digit:]]','~'),'~')-1),1)) = 7 then
substr(ltrim(substr(FULL_CALLING_STATION_ID,instr(
FULL_CALLING_STATION_ID,'sip:')+4,
instr(REGEXP_REPLACE(substr(FULL_CALLING_STATION_ID,
instr(FULL_CALLING_STATION_ID,'sip:')+4,100),
'[^[:digit:]]','~'),'~')-1),1),1,3)||ltrim(substr(
REGEXP_REPLACE(PRIMARY_ROUTING_NUMBER,'[+]',NULL)
,instr(REGEXP_REPLACE(PRIMARY_ROUTING_NUMBER,'[+]',NULL),'sip:')+4,
instr(REGEXP_REPLACE(substr(REGEXP_REPLACE(
PRIMARY_ROUTING_NUMBER,'[+]',NULL),
instr(REGEXP_REPLACE(PRIMARY_ROUTING_NUMBER,'[+]',
NULL),'sip:')+4,100),'[^[:digit:]]','~'),'~')-1),1) else ltrim(substr(
REGEXP_REPLACE(
PRIMARY_ROUTING_NUMBER,'[+]',NULL),
instr(REGEXP_REPLACE(
PRIMARY_ROUTING_NUMBER,'[+]',NULL),'sip:')+4,
instr(REGEXP_REPLACE(substr(REGEXP_REPLACE(
PRIMARY_ROUTING_NUMBER,'[+]',NULL),
instr(REGEXP_REPLACE(PRIMARY_ROUTING_NUMBER,'[+]',
NULL),'sip:')+4,100),
'[^[:digit:]]','~'),'~')-1),1) end,'Unavailable')
where summary_date  = to_date('20081201','YYYYMMDD')

Fun yet scary.

Clean up phone numbers

I needed to clean up a list of phone numbers today. The phone numbers where in a varchar2 column in the database and were listed like this: (916) 555 -1212.

I knew I could used some instr and substr functions to cut the data out, but I wanted to use some of the regex functions found in Oracle 10G and above. This handy little piece of code simply removed all non numeric digits from a string.

select REGEXP_REPLACE(CALLNUMBER,'[^[:digit:]]', NULL)
from temp_cima where rownum < 11;