RE: %check() workalike in SQL

["Hauser, Birgitta" <Birgitta.Hauser@xxxxxxxxxxx>]

Hi,

there is no function like %Check in SQL.
You either can write an external function where the Check is done by the RPG
%Check-Function or you may use a combination of the functions LENGTH/TRIM
and TRANSLATE

Something like this:

Select Case When Length(Trim(Translate(MyCharFld, '  ', '1234567890')))<> 0 
            Then 'Invalid Data'
            Else 'Valid Data'
       End as CheckFld, MyCharFld
> From MyTable

Birgitta
      


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of AGlauser@xxxxxxxxxxxx
Sent: Tuesday, October 24, 2006 17:28
To: Midrange technical discussions
Subject: %check() workalike in SQL

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

############################################################################
#########
Attention:
The above message and/or attachment(s) is private and confidential and is
intended 
only for the people for which it is addressed. If you are not named in the
address 
fields, ignore the contents and delete all the material. Thank you. Have a
nice day.

For more information on email virus scanning, security and content
management, please contact administrator@xxxxxxxxxxxx
############################################################################
#########