Printing Numerics from Alphanumeric Column in Oracle

bashamsc's Avatar author of Printing Numerics from Alphanumeric Column in Oracle
This is an article on Printing Numerics from Alphanumeric Column in Oracle in Oracle.
Rated 5.00 By 1 users
Let us suppose we have a table T_APHA_NUMERIC which consists alphanumeric column as shown below
Code:
APHA_NUMERIC

CDNTK1V0103H
123ABC45NJDK
ABC12345
12345
ABC5TEST05
GO4EXPERT
4758501
SACIN10DULKAR
Let us suppose we have two requirements as below

1. We need to print only numeric values as shown below
Code:
10103
12345
12345
12345
505
4
4758501
10
2. We need to print values which are having only numeric values as shown below
Code:
12345
4758501
Below query will achieve first scenario
Code:
select replace(translate(APHA_NUMERIC,'ABCDEFGHIJKLMNOPQRSTUVXYZ','&'),'&') num from T_APHA_NUMERIC;
To know how the query will work we should know how translate function works.

The translate function will replaces one set of characters to another.

For example the below query will replace the alphabets with '&'.

Code:
select translate('ABC5TEST05','ABCDEFGHIJKLMNOPQRSTUVXYZ','&') as trans from dual
Output will be

Code:
&505
The translate function is searching for alphabet series and replacing with special character '&' which we have given as replacement character.

On the output of the above we are using replace function to replace special character '&' with null.

So , the below query will return only numeric values which are present in the Alpha Numeric column.

Code:
select replace(translate(APHA_NUMERIC,'ABCDEFGHIJKLMNOPQRSTUVXYZ','&'),'&') num from T_APHA_NUMERIC;
Output will be as below

Code:
10103
12345
12345
12345
505
4
4758501
10
Below query will achieve second scenario
Code:
SELECT * FROM 
(
SELECT CASE WHEN  length(translate(APHA_NUMERIC,'ABCDEFGHIJKLMNOPQRSTUVXYZ','&'))=length(APHA_NUMERIC) 
                    THEN APHA_NUMERIC 
                    ELSE  null 
                    END   
FROM   T_APHA_NUMERIC
) 
WHERE APHA_NUMERIC IS NOT NULL
In the above query we checking the length of tranlate column with APHA_NUMERIC column. If the length is same we are printing APHA_NUMERIC else null.

If the column is not having any alphabets in the column translate will not replace anything with special character '&'. So the length will match with length of the column if the column contains only numeric values.

The above query will print only numeric values which not having any alphabets in it.

The output will be as below

Code:
12345
4758501
shabbir like this