CODEWITHSUNDEEP
sql-server
Haoest
Haoest

Reputation: 13896

Creating column with user defined constraint

I want to limit my varchar columns to have only ascii characters within a specified range, say 0-9 or A-F (for hex characters) What would my constraint look like?

Upvotes: 0

Views: 3726

Answers (4)

Amit Naidu
Amit Naidu

Reputation: 2638

Etny showed an elegant trick using a double negative ! Otherwise to validate hex numbers, I'd have to do something like (Mac LIKE '[a-f0-9][a-f0-9][a-f0-9]'..12 times) since I'm validating a MAC address and annoyingly, you cant specify repetition like \d{12} in regex. Plus I needed it to have a fixed size, so here is my adaptation:

CREATE TABLE #HexTest
(
    Mac varchar(12)
        CONSTRAINT CK_Mac_12Hex_Only --names make error msgs useful!
        CHECK (Mac NOT LIKE '%[^a-f0-9]%' AND LEN(Mac) = 12)  
)

INSERT INTO #HexTest values('5ca1ab1eD00d')     -- good
INSERT INTO #HexTest values('DeadDa7aba5e')     -- good
INSERT INTO #HexTest values('Dad`sDebac1e')     -- bad chars
INSERT INTO #HexTest values('Ca5cadab1e')       -- too short
INSERT INTO #HexTest values('Deba7ab1eDeal')    -- too long, fails for size    
DROP TABLE #HexTest

I was trying to do something like this:

CHECK (len(CONVERT(binary(6), Mac, 2)) = 6)

(which works in Oracle with hextoraw), but it throws an error for invalid values here. So it didn't work. I wish there was a function that tries to parse and just returns null when there is an error. Maybe something like Parse vs. TryParse or maybe if IsNumeric worked for Hex...

Upvotes: 0

Etni
Etni

Reputation:

Here's something that should work:

CREATE TABLE #TMP
(
    TEST nvarchar(20) CHECK ( TEST NOT LIKE '%[^A-F0-9]%' )  
)

INSERT INTO #TMP values('A1') -- WORKS
INSERT INTO #TMP values('G1') -- FAILS

Upvotes: 2

tpdi
tpdi

Reputation: 35141

You're storing a number. Your column type should therefore be a number, an int.

On display, convert that number to a hexadecimal representation, either in the frontend, or with a User Defined Function on the server.

Upvotes: 1

Jason Coyne
Jason Coyne

Reputation: 6636

You can easily apply a regex expression to a check constraint to do this.

CREATE TABLE [Account]
(
    [AccountNumber] nvarchar(20) CHECK (dbo.RegexMatch( 
        [AccountNumber], '^[A-Z]{3,5}\d{5}-\d{3}$' ) = 1),
    [PhoneNumber] nchar(13) CHECK (dbo.RegexMatch( 
        [PhoneNumber], '^\(\d{3}\)\d{3}-\d{4}$' ) = 1),
    [ZipCode] nvarchar(10) CHECK (dbo.RegexMatch( 
        [ZipCode], '^\d{5}(\-\d{4})?$' ) = 1)
)

Upvotes: 2

Related Questions