Home » SQL & PL/SQL » SQL & PL/SQL » Extracting numbers - Query (Oracle 10g, Windows XP)
Extracting numbers - Query [message #441704] Tue, 02 February 2010 16:47 Go to next message
Pranitha
Messages: 5
Registered: January 2010
Junior Member
Hi All,

I have a table which consists of telephone numbers. But these telephone numbers entered are incorrect.
Say unknowingly they have typed some wild characters or some alphabets in between the numbers or they might have not entered the country code(1).

Now I have to eliminate all the extra characters other than the numeric digits from the telephone numbers.

For this I have written the query and the query is returning me the correct results but I think this query can further shortened.

Can anyone please help me in turning this query more efficient.

Here is the test table:

SELECT '1*/(126)b/*-803_c+$.,1703' AS num FROM DUAL
UNION
SELECT '1a636b051c0164' AS num FROM DUAL
UNION
SELECT '>17,76^46_678/02' AS num From DUAL
UNION
SELECT '56/_56c78A45:8x5' AS num FROM DUAL
UNION
SELECT '(1-678)-a290,8724' AS num FROM DUAL


Here is my query:
As I have to eliminate all the wild characters and the alphabets I have included them in my translate function.

SELECT
    (CASE 
        WHEN length(REPLACE(TRANSLATE(UPPER(A.num),'ABCDEFGHIJKLMNOPQRSTUVWXYZ`~!@#$%^&*()-_+=|\[]{};:<>,.?/"',' '),' ','')) = 11
        THEN substr(REPLACE(TRANSLATE(UPPER(A.num),'ABCDEFGHIJKLMNOPQRSTUVWXYZ`~!@#$%^&*()-_+=|\[]{};:<>,.?/"',' '),' ',''),1,1)||' '||
             substr(REPLACE(TRANSLATE(UPPER(A.num),'ABCDEFGHIJKLMNOPQRSTUVWXYZ`~!@#$%^&*()-_+=|\[]{};:<>,.?/"',' '),' ',''),2,3)||' '||
             substr(REPLACE(TRANSLATE(UPPER(A.num),'ABCDEFGHIJKLMNOPQRSTUVWXYZ`~!@#$%^&*()-_+=|\[]{};:<>,.?/"',' '),' ',''),5,3)||' '||
             substr(REPLACE(TRANSLATE(UPPER(A.num),'ABCDEFGHIJKLMNOPQRSTUVWXYZ`~!@#$%^&*()-_+=|\[]{};:<>,.?/"',' '),' ',''),8,4)
        WHEN length(REPLACE(TRANSLATE(UPPER(A.num),'ABCDEFGHIJKLMNOPQRSTUVWXYZ`~!@#$%^&*()-_+=|\[]{};:<>,.?/"',' '),' ','')) = 10 
        THEN '1'||' '||
             substr(REPLACE(TRANSLATE(UPPER(A.num),'ABCDEFGHIJKLMNOPQRSTUVWXYZ`~!@#$%^&*()-_+=|\[]{};:<>,.?/"',' '),' ',''),1,3)||' '||
             substr(REPLACE(TRANSLATE(UPPER(A.num),'ABCDEFGHIJKLMNOPQRSTUVWXYZ`~!@#$%^&*()-_+=|\[]{};:<>,.?/"',' '),' ',''),4,3)||' '||
             substr(REPLACE(TRANSLATE(UPPER(A.num),'ABCDEFGHIJKLMNOPQRSTUVWXYZ`~!@#$%^&*()-_+=|\[]{};:<>,.?/"',' '),' ',''),7,4)  
    END) AS Corrected_NUM 
FROM
(
SELECT '1*/(126)b/*-803_c+$.,1703' AS num FROM DUAL
UNION
SELECT '1a636b051c0164' AS num FROM DUAL
UNION
SELECT '>17,76^46_678/02' AS num From DUAL
UNION
SELECT '56/_56c78A45:8x5' AS num FROM DUAL
UNION
SELECT '(1-678)-a290,8724' AS num FROM DUAL
) A


Thanks in Advance.
Re: Extracting numbers - Query [message #441708 is a reply to message #441704] Tue, 02 February 2010 17:12 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
http://www.orafaq.com/forum/m/204907/136107/?srch=regex+number#msg_204907

Re: Extracting numbers - Query [message #441711 is a reply to message #441704] Tue, 02 February 2010 17:44 Go to previous message
Pranitha
Messages: 5
Registered: January 2010
Junior Member
Hi,

Thanks a lot for your quick reply.

Today i got to learn 'Regular Expressions'

Thanks,
Pranitha
Previous Topic: PL/SQL Table
Next Topic: Union
Goto Forum:
  


Current Time: Sat Sep 24 21:28:02 CDT 2016

Total time taken to generate the page: 0.06903 seconds