Printing Numerics from Alphanumeric Column in Oracle

Discussion in 'Oracle' started by bashamsc, May 27, 2013.

  1. bashamsc

    bashamsc New Member

    Joined:
    May 22, 2007
    Messages:
    51
    Likes Received:
    7
    Trophy Points:
    0
    Location:
    chennai
    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 likes this.

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice