Re: %check() workalike in SQL

["Raul A. Jager W." <raul@xxxxxxxxxx>]

"TRANSLATE" all alphabetical characters (and symbols) to blanks, then 
trim...

AGlauser@xxxxxxxxxxxx wrote:

> Hi all,
>
> I've looked through the SQL reference for DB2 for iSeries, and haven't 
> found anything yet.  Hoping someone can suggest a combination of functions 
> or one that I missed that can work like the %check() RPG opcode.  %check( 
> verification string : search string) returns the first occurrence of a 
> character in 'search string' that does not appear in 'verification 
> string'.
>
> The best I've come up with so far is:
>
> select  CASE 
>         WHEN SUBST(prtnum, 1, 1) IN ('0','1','2','3','4','5','6','7','8',
> '9') THEN prtnum
>         WHEN SUBST(prtnum, 2, 1) IN ('0','1','2','3','4','5','6','7','8',
> '9') THEN SUBST(prtnum, 2)
>         WHEN SUBST(prtnum, 3, 1) IN ('0','1','2','3','4','5','6','7','8',
> '9') THEN SUBST(prtnum, 3)
>
> and so on (for 25 characters).  Please be gentle, I think it's just as 
> ugly as you do :)
>
> The requirement is to extract the numeric portion of a part number, to try 
> to group part numbers "logically".  For example, GR1234 might be similar 
> to RG1234, 1234ABC, and ABC1234DEF.  I expect I'll have to end up using 
> either embedded SQL, an stored procedure, or some other programming 
> solution.
>
> All suggestions welcome,
> Adam
>