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