Reputation: 319
I want to use regex to restrict users to enter special characters, numbers and alphabets except UPPERCASE by adding a constraint while creating a table. User is allowed to enter only UPPERCASE alphabets. I have tried the following.
CONSTRAINT chk_spl_chars_model CHECK (Model LIKE '%[A-Z]+%')
This didnt work. It is not allowing to enter any value. Any Help is appreciated.
Following is the query u yse to create table
CREATE TABLE Cars(
Model VARCHAR(10) NOT NULL
,CONSTRAINT chk_spl_chars_model CHECK (Model LIKE '%^[A-Z]+$%')
);
This is accepting any value to be inserted even lowercase and special characters
Upvotes: 0
Views: 2577
Reputation: 239824
You need to use a case sensitive collation, as mehdi's answer indicated, but you also need to change what you're looking for:
CREATE TABLE Cars(
Model VARCHAR(10) collate Latin1_General_CS_AI NOT NULL
,CONSTRAINT chk_spl_chars_model CHECK (
Model
NOT LIKE '%[^ABCDEFGHIJKLMNOPQRSTUVWXYZ]%')
);
You want:
NOT LIKE
because we want to search for characters that we don't want to appear%
matches "any character" so we want to match any number of characters^
within a []
block indicates to match any character not included in the setABCDEFGHIJKLMNOPQRSTUVWXYZ
because even in most case sensitive collations, lower case letters appear between upper case letters if you use a range expression like A-Z
.So the complete pattern says "match any number of characters, then one character that isn't an upper case character, then any number of characters" - and so, in other words, it matches any string that contains at least one non-upper case character - and if we match that, then we reject that value (because of the NOT LIKE
).
^
(outside of []
), $
and +
have no special meaning for LIKE
patterns.
Upvotes: 1
Reputation: 11611
You must using collation in your constraint:
CONSTRAINT chk_spl_chars_model CHECK (not (Model COLLATE Latin1_General_100_CS_AI LIKE '%[a-z]%'))
You can using following format too (change collation of model column):
CREATE TABLE Cars(
Model VARCHAR(10) COLLATE Latin1_General_100_CS_AI NOT NULL
,CONSTRAINT chk_spl_chars_model CHECK (NOT Model LIKE '%[a-z]%')
);
Upvotes: 1