Square DBA – Oracle DBA Talk

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.