I am tring to clean up data in some of our fields. The requirement is
to only have letter (a-z,A-Z) and number (0-9) characters in the
field...

So, if we select from the non-cleaned-up field, we may get A_p@;Pl%e2
the cleaned-up field should then contain ApPle2.

I know it can be done simply with a java function, but before we go
that route, I wanted to see if there was anyway to do it w/ SQL only..

Eventually, we will want to make a trigger, so as data is inserted into
the non clean field, the cleaned up field is automatically populated.

I have not yet found any DB2 functions which will do this, other then
doing a substring and case through each character, but that will make
some awefull looking SQL..

Any thoughts? Thanks

Regards,
Jay

This also check all characters one by one. But, it is a little easier
to check each character.

CREATE FUNCTION TO_ALPHANUMp (inStr VARCHAR(4000))
RETURNS VARCHAR(4000)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE non_an VARCHAR(4000);
DECLARE anStr VARCHAR(4000);
DECLARE slen, seq INTEGER;

SET non_an =
TRANSLATE(inStr,'','abcdefghijklmnopqrstuvwxyzABCD EFGHIJKLMNOPQRSTUVWXYZ0123456789');
SET slen = LENGTH(inStr);
SET seq = 1;
SET anStr = '';

WHILE seq <= slen DO
IF SUBSTR(non_an, seq, 1) = ' ' THEN
SET anStr = anStr || SUBSTR(inStr, seq, 1);
END IF;
SET seq = seq + 1;
END WHILE;
RETURN anStr;
END@

Perhaps loop through a FUNCTION that checks each digit?

CREATE FUNCTION AlphaNumeric(Digit VARCHAR(1))
RETURNS VARCHAR(1)
SPECIFIC AlphaNumeric
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
CASE
WHEN ASCII(Digit) BETWEEN 48 AND 57
OR ASCII(Digit) BETWEEN 65 AND 90
OR ASCII(Digit) BETWEEN 97 AND 122 THEN Digit
ELSE ''
END

B.

You can check it letter by letter. As Tonkuma said with TRANSLATE, or
use BETWEEN on the ASCII() value.

CREATE FUNCTION Is_AlphaNumeric(Digit VARCHAR(1))
RETURNS INTEGER
SPECIFIC Is_AlphaNumeric
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
CASE
WHEN ASCII(Digit) BETWEEN 48 AND 57
OR ASCII(Digit) BETWEEN 65 AND 90
OR ASCII(Digit) BETWEEN 97 AND 122 THEN 1
ELSE 0
END

And use it on each character.

B.

many thanks for the suggestions, i will look into them.. database is
utf-8, so i will have to check w/ requirements how to handle non-ascii
characters...

ok, so i got a little more info, and it looks like support for
non-ascii characters will be required, similar to the java
isLetterOrDigit method... have not seen something like that in db2
yet.. my search continues.. thx for the help

Jason Knaster wrote:[color=blue]
> ok, so i got a little more info, and it looks like support for
> non-ascii characters will be required, similar to the java
> isLetterOrDigit method... have not seen something like that in db2
> yet.. my search continues.. thx for the help
>[/color]
You can wrap this Java method into an DB2 UDF.
Won't break any speed records, but I hope you don't do this a billion
times :-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab