CODEWITHSUNDEEP
mysqlsqlsql-server
Ben
Ben

Reputation: 7

SQL Check Constraint make value allow only part numeric and other part just letters

I've been looking for ages to try and find an answer to this problem but haven't found an example of this being done to help me work out how to implement it.

I'm creating a table and I want a column in MS SQL server which is CHAR(5) datatype but I want it to only allow first 2 CHARS of it to have a letter and following 3 CHARS to be numbers only. I've seen things like "CHECK (UnitCode NOT LIKE '%[^A-Z0-9]%') " which limits to only letters and numbers, but doesn't force to only allow which chars can or can't be numbers or letters.

IF someone can point me in the right direction I'd really appreciate it. Thanks.

Upvotes: 0

Views: 3104

Answers (2)

juanho
juanho

Reputation: 37

you should specify a constraint check when creating the table:

CREATE TABLE table_with_data ( data_to_check CHAR(5), CONSTRAINT data_format_chk CHECK ( (SUBSTRING(data_to_check FROM 1 FOR 2) LIKE '[a-zA-Z]') AND (CAST(SUBSTRING(data_to_check FROM 3 FOR 3) AS SIGNED) BETWEEN 0 AND 999) ) );

Upvotes: 0

Pondlife
Pondlife

Reputation: 16240

WHERE column LIKE '[A-Z][A-Z][0-9][0-9][0-9]'

Assuming that a 'letter' really is A-Z and nothing else (e.g. accented or non-European characters).

Upvotes: 4

Related Questions