Re: %check() workalike in SQL

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