Recipe14.6.Searching for Mixed Alphanumeric Strings

Recipe 14.6. Searching for Mixed Alphanumeric Strings

Problem

You have a column with mixed alphanumeric data. You want to return those rows that have both alphabetical and numeric characters; in other words, if a string has only number or only letters, do not return it. The return values should have a mix of both letters and numbers. Consider the following data:

 STRINGS ------------ 1010 switch 333 3453430278 ClassSummary findRow 55 threes 

The final result set should contain only those rows that have both letters and numbers:

 STRINGS ------------ 1010 switch findRow 55 

Solution

Use the built-in function TRANSLATE to convert each occurrence of a letter or digit into a specific character. Then keep only those strings that have at least one occurrence of both. The solution uses Oracle syntax, but both DB2 and PostgreSQL support TRANSLATE, so modifying the solution to work on those platforms should be trivial:

 with v as ( select 'ClassSummary' strings from dual union select '3453430278'           from dual union select 'findRow 55'           from dual union select '1010 switch'          from dual union select '333'                  from dual union select 'threes'               from dual ) select strings   from ( select strings, translate( strings, 'abcdefghijklmnopqrstuvwxyz0123456789',          rpad('#',26,'#')||rpad('*',10,'*')) translated from v ) x where instr(translated,'#') > 0 and instr(translated,'*') > 0 

As an alternative to the WITH clause, you may use an inline view or simply create a view.

Discussion

The TRANSLATE function makes this problem extremely easy to solve. The first step is to use TRANSLATE to identify all letters and all digits by pound (#) and asterisk (*) characters, respectively. The intermediate results (from inline view X) are as follows:

  with v as ( select 'ClassSummary' strings from dual union select '3453430278'           from dual union select 'findRow 55'           from dual union select '1010 switch'          from dual union select '333'                  from dual union select 'threes'               from dual ) select strings,        translate(          strings,          'abcdefghijklmnopqrstuvwxyz0123456789',          rpad('#',26,'#')||rpad('*',10,'*')) translated   from v STRINGS       TRANSLATED ------------- ------------ 1010 switch   **** ###### 333           *** 3453430278    ********** ClassSummary  C####S###### findRow 55    ####R## ** threes        ###### 

At this point, it is only a matter of keeping those rows that have at least one instance each of "#" and "*". Use the function INSTR to determine whether "#" and "*" are in a string. If those two characters are, in fact, present, then the value returned will be greater than zero. The final strings to return, along with their translated values, are shown next for clarity:

  with v as ( select 'ClassSummary' strings from dual union select '3453430278'           from dual union select 'findRow 55'           from dual union select '1010 switch'          from dual union select '333'                  from dual union select 'threes'               from dual ) select strings, translated   from ( select strings,        translate(          strings,          'abcdefghijklmnopqrstuvwxyz0123456789',          rpad('#',26,'#')||rpad('*',10,'*')) translated   from v        )  where instr(translated,'#') > 0    and instr(translated,'*') > 0 STRINGS      TRANSLATED ------------ ------------ 1010 switch  **** ###### findRow 55   ####R## **