Constraint to allow all numers and alpha

H2o · #1 (**permalink**) 08-06-2007, 12:40 PM

Does anyone know how to develop a check constraint in oracle that will only allow digits 0-9 and a dash? Thank you ahead of time.

priyan · #2 (**permalink**) 08-06-2007, 11:21 PM

Hi,

Refer the details given below..

Quote:

CREATE TABLE check_test
(
id INTEGER,
value VARCHAR2(3),
CONSTRAINT chk_value CHECK(value IN ('0', '1','2','3','4','5','6','7','8','9','-'))
);
Table created

INSERT INTO check_test(id, value) VALUES (1, '2')
1 row inserted
INSERT INTO check_test(id, value) VALUES (1, '9')
1 row inserted
INSERT INTO check_test(id, value) VALUES (1, '-')
1 row inserted

INSERT INTO check_test(id, value) VALUES (1, '10')
ORA-02290: check constraint (PMXUSERS.CHK_VALUE) violated;

INSERT INTO check_test(id, value) VALUES (1, '--')
ORA-02290: check constraint (PMXUSERS.CHK_VALUE) violated

hope this will do... !!

bluesky · #3 (**permalink**) 08-07-2007, 01:27 AM

Something like...

CHECK (LTRIM (column_name, '0123456789-') IS NULL)

...of course depending on version you may be able to use a regular expression here.

H2o · #4 (**permalink**) 08-07-2007, 01:29 AM

Is there a way to format it like a zip code, for instance the field is zip code, I want to allow either a format like this 99999 or 99999-9999. Is there a constraint that will validate against either format and check for the "-" as well?

Please let me know and thank you for your answer!!!

prasannavigneshr · #5 (**permalink**) 08-20-2007, 07:12 AM

SQL>CREATE TABLE TEST_TABLE
2 (
3 number_and_ VARCHAR2(30)
4 CONSTRAINT p_check
5 CHECK ( REGEXP_LIKE ( number_and_, '^[0-9\-]*$' ) )
6 )
7 /

Table created.

SQL>insert into TEST_TABLE values('-');

1 row created.

SQL>insert into TEST_TABLE values('0-9');

1 row created.

SQL>insert into TEST_TABLE values('0-9222-');

1 row created.

SQL>insert into TEST_TABLE values('0-9222-*');
insert into TEST_TABLE values('0-9222-*')
*
ERROR at line 1:
ORA-02290: check constraint (P_CHECK) violated

prasannavigneshr · #6 (**permalink**) 08-20-2007, 07:22 AM

Pls visit

4 Using Regular Expressions in Oracle Database

suresh · #7 (**permalink**) 08-20-2007, 07:28 AM

> Is there a way to format it like a zip code, for
> instance the field is zip code, I want to allow
> either a format like this 99999 or 99999-9999. Is
> there a constraint that will validate against either
> format and check for the "-" as well?
>
> Please let me know and thank you for your answer!!!

create table test (str varchar2(100) constraint t_check check(translate(str,'012345678','999999999') in ('99999','99999-9999')))

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

LinkBacks (?) LinkBack to this Thread: http://www.discussweb.com/database-support/3168-constraint-allow-all-numers-alpha.html
Posted By	For	Type	Date
DiscussWeb IT Community - Technical Support and Technology Discussions	This thread	Refback	08-20-2007 08:12 AM

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
What is alpha an beta testing?	devarajan.v	Software Testing	1	07-16-2007 10:52 PM