DB2 CHECK for names?

[Michael Huber]

Hallo,

does anyone know how to perform a

CREATE TABLE TEST(NAME CHAR(40) NOT NULL, CHECK(NAME ???));

I want that i only can insert lower and upper chars. I tried it with the statement

CHECK(NAME BETWEEN 'a' AND 'z') OR (NAME BETWEEN 'A' AND 'Z')

but this only checks the first char of the inserted NAME. Can anyone help me?

Thanks very much!

[Douglas Doole]

Try:

TRANSLATE(NAME, '!', ' aAbBcC...xXyYzZ) = ''

(You have to fill in all the letters where I used "...")

This will replace space with ! and all the letters with spaces. (Effectively
leaving a non-space character for evey non-letter. You then compare it to a
blank string.
--
__________________________________________________ ___________________
Doug Doole
DB2 Universal Database Development
IBM Toronto Lab

Visit the DB2 UDB and DB2 Connect Online Support site at:
http://www.ibm.com/software/data/db2...s2unix/support

[Michael Huber]

Thank you! After thinking about it, I think I understood it :-) I only
have one more Problem: The NAME field is a CHAR(30) field and am I
right that if I insert a name of the Length 15 the rest will be filled
with '!'? Then the comparison will fail and the CHECK returns false?
So I did this:

CHECK(TRANSLATE(NNAME, '', '
aAbBcCdDeEfFgGhHiIjJkKlLmMnNoOpPqQrRsStTuUvVwWxXyY zZ') = '')

But there is the problem that blanks are allowed in the NAME!
Douglas Doole <do***@ca.ibm.com> wrote in message news:<bp**********@hanover.torolab.ibm.com>...

Try:

TRANSLATE(NAME, '!', ' aAbBcC...xXyYzZ) = ''

(You have to fill in all the letters where I used "...")

This will replace space with ! and all the letters with spaces. (Effectively
leaving a non-space character for evey non-letter. You then compare it to a
blank string.

[Tokunaga T.]

How about this?
CHECK(TRANSLATE(RTRIM(NAME), '!',
' aAbBcCdDeEfFgGhHiIjJkKlLmMnNoOpPqQrRsStTuUvVwWxXyY zZ') = '')

fs*****@hotmail.com (Michael Huber) wrote in message news:<8f**************************@posting.google. com>...

Thank you! After thinking about it, I think I understood it :-) I only
have one more Problem: The NAME field is a CHAR(30) field and am I
right that if I insert a name of the Length 15 the rest will be filled
with '!'? Then the comparison will fail and the CHECK returns false?
So I did this:

CHECK(TRANSLATE(NNAME, '', '
aAbBcCdDeEfFgGhHiIjJkKlLmMnNoOpPqQrRsStTuUvVwWxXyY zZ') = '')

But there is the problem that blanks are allowed in the NAME!
Douglas Doole <do***@ca.ibm.com> wrote in message news:<bp**********@hanover.torolab.ibm.com>...

Try:

TRANSLATE(NAME, '!', ' aAbBcC...xXyYzZ) = ''

(You have to fill in all the letters where I used "...")

This will replace space with ! and all the letters with spaces. (Effectively
leaving a non-space character for evey non-letter. You then compare it to a
blank string.