Recipe7.14.Converting Alphanumeric Strings into Numbers

Recipe 7.14. Converting Alphanumeric Strings into Numbers

Problem

You have alphanumeric data and would like to return numbers only. You want to return the number 123321 from the string "paul123f321".

Solution

DB2

Use the functions TRANSLATE and REPLACE to extract numeric characters from an alphanumeric string:

 1 select cast( 2        replace( 3      translate( 'paul123f321', 4                 repeat('#',26), 5                 'abcdefghijklmnopqrstuvwxyz'),'#','') 6        as integer ) as num 7   from t1 

Oracle and PostgreSQL

Use the functions TRANSLATE and REPLACE to extract numeric characters from an alphanumeric string:

 1 select cast( 2        replace( 3      translate( 'paul123f321', 4                 'abcdefghijklmnopqrstuvwxyz', 5                 rpad('#',26,'#')),'#','') 6        as integer ) as num 7   from t1 

MySQL and SQL Server

As of the time of this writing, neither vendor supports the TRANSLATE function, thus a solution will not be provided.

Discussion

The only difference between the two solutions is syntax; DB2 uses the function REPEAT rather than RPAD and the parameter list for TRANSLATE is in a different order. The following explanation uses the Oracle/PostgreSQL solution but is relevant to DB2 as well. If you run query inside out (starting with TRANSLATE only), you'll see this is very simple. First, TRANSLATE converts any non-numeric character to an instance of "#":

  select translate( 'paul123f321',                   'abcdefghijklmnopqrstuvwxyz',                   rpad('#',26,'#')) as num   from t1 NUM ----------- ####123#321 

Since all non-numeric characters are now represented by "#", simply use REPLACE to remove them, then cast the result to a number. This particular example is extremely simple because the data is alphanumeric. If additional characters can be stored, rather than fishing for those characters, it is easier to approach this problem differently: rather than finding non-numeric characters and then removing them, find all numeric characters and remove anything that is not amongst them. The following example will help clarify this technique:

  select replace(      translate('paul123f321',        replace(translate( 'paul123f321',                           '0123456789',                           rpad('#',10,'#')),'#',''),                rpad('#',length('paul123f321'),'#')),'#','') as num   from t1 NUM ----------- 123321 

This solution looks a bit more convoluted than the original but is not so bad once you break it down. Observe the innermost call to TRANSLATE:

  select translate( 'paul123f321',                   '0123456789',                   rpad('#',10,'#'))   from t1 TRANSLATE(' ----------- paul###f### 

So, the initial approach is different; rather than replacing each non-numeric character with an instance of "#", you replace each numeric character with an instance of "#". The next step removes all instances of "#", thus leaving only non-numeric characters:

  select replace(translate( 'paul123f321',                           '0123456789',                           rpad('#',10,'#')),'#','')   from t1 REPLA ----- paulf 

The next step is to call TRANSLATE again, this time to replace each of the non-numeric characters (from the query above) with an instance of "#" in the original string:

  select translate('paul123f321',        replace(translate( 'paul123f321',                           '0123456789',                           rpad('#',10,'#')),'#',''),                rpad('#',length('paul123f321'),'#'))   from t1 TRANSLATE(' ----------- ####123#321 

At this point, stop and examine the outermost call to TRANSLATE. The second parameter to RPAD (or the second parameter to REPEAT for DB2) is the length of the original string. This is convenient to use since no character can occur enough times to be greater than the string it is part of. Now that all non-numeric characters are replaced by instances of "#", the last step is to use REPLACE to remove all instances of "#". Now you are left with a number.