How to use CHECK constraint to restrict non-numeric varchar2 (only alphabets)?

Question

I have a column NAME
It must contain only characters and not numbers
How do I use CHECK condition:

CHECK(NAME NOT LIKE '%[0-9]%')

or any other method...

edit: Oracle database is used.

LIKE (at least in the SQL Standard) does not support regular expressions. — , Jan 06 '12 at 10:46

score 4 · Accepted Answer · answered Jan 06 '12 at 10:48

4

You didn't state your DBMS so I'm assuming PostgreSQL

CHECK(name ~ '^[^0-9]*$')

answered Jan 06 '12 at 10:48

score 3 · Answer 2 · edited May 23 '17 at 11:53

3

Double negative Should be standard (not MySQL though) because it uses LIKE:

CHECK(NAME NOT LIKE '%[^a-zA-Z]%')

edited May 23 '17 at 11:53

Community

answered Jan 06 '12 at 11:01

gbn

That's **not** standard SQL, as the standard does not support regular expressions for LIKE. Which DBMS does support this kind of syntax? – Jan 06 '12 at 12:20
SQL server, sybase. It isn't refectory either – gbn Jan 06 '12 at 12:27
what does "*refectory*" mean? (I'm not a native speaker) – Jan 06 '12 at 12:28
He he. Spell check on my phone... regex I mean – gbn Jan 06 '12 at 14:27

2 Answers2