How to specify input format in SQL create table?

Question

I'm new in SQL and I need to create table with specified field format. How to add CHECK condition that will assure that input will be formatted e.g.

[LLLDD]

where L is a letter and D is a digit?

BICube · Accepted Answer · 2015-05-10 18:25:13Z

3

Try this if you are adding the constraint on a new table

CONSTRAINT ck_data_checker CHECK ([columnName] LIKE ('[A-Z][A-Z][A-Z][0-9][0-9]'))

Try this if you are adding the constraint on existing table

  ALTER TABLE tableName
  ADD CONSTRAINT ck_data_checker CHECK ([columnName] LIKE ('[A-Z][A-Z][A-Z][0-9][0-9]'))

answered May 10, 2015 at 18:08

BICube

4,7211 gold badge26 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

zedfoxus · Accepted Answer · 2015-05-10 18:10:27Z

1

create table test (
  field1 char(5),
  check (field1 like '[a-z][a-z][a-z][0-9][0-9]')
);

insert into test values ('ttt09'); --this will succeed

If you were to change the insert to:

insert into test values ('testi'); -- this will fail
insert into test values ('12345'); -- this will fail

answered May 10, 2015 at 18:10

zedfoxus

37.4k5 gold badges68 silver badges66 bronze badges

haronaut · Accepted Answer · 2015-05-10 18:22:57Z

0

I'm no sql server expert, but I think you can add a LIKE with a regular expression. Have a look at these websites

answered May 10, 2015 at 18:22

haronaut

4033 silver badges16 bronze badges