Selecting only letter and number characters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nycjay@yahoo.com - 09 Mar 2006 23:43 GMT

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

Reply to this Message

Tonkuma - 10 Mar 2006 02:34 GMT

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,'','abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789');
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@

Reply to this Message

Brian Tkatch - 10 Mar 2006 15:48 GMT

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.

Reply to this Message

Brian Tkatch - 10 Mar 2006 16:13 GMT

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.

Reply to this Message

Jason Knaster - 10 Mar 2006 19:34 GMT

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...

Reply to this Message

Jason Knaster - 10 Mar 2006 23:53 GMT

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

Reply to this Message

Serge Rielau - 11 Mar 2006 13:05 GMT

Jason Knaster wrote:

> 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

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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message