By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,514 Members | 1,702 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,514 IT Pros & Developers. It's quick & easy.

Selecting only letter and number characters

P: n/a
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

Mar 9 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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@

Mar 10 '06 #2

P: n/a
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.

Mar 10 '06 #3

P: n/a
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.

Mar 10 '06 #4

P: n/a
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...

Mar 10 '06 #5

P: n/a
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

Mar 11 '06 #6

P: n/a
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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 11 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.