CODEWITHSUNDEEP
sqlsql-serverregexsql-server-2008
Midhun Mathew
Midhun Mathew

Reputation: 319

Regex that restrict special characters, numbers and alphabets except UPPERCASE

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

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

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:

  • a 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
  • then ^ within a [] block indicates to match any character not included in the set
  • ABCDEFGHIJKLMNOPQRSTUVWXYZ 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

mehdi lotfi
mehdi lotfi

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

Related Questions