Creating column with user defined constraint

Question

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?

score 2 · Accepted Answer · answered Apr 10 '09 at 20:41

2

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)
)

answered Apr 10 '09 at 20:41

Jason Coyne

6,509
8
40
70

I get this message when testing the function with "select db.RegexMatch('abc123', '[a-z]+')" Cannot find either column "dbo" or the user-defined function or aggregate "dbo.RegexMatch", or the name is ambiguous. – Haoest Apr 10 '09 at 21:09
@Haoest: That's because there is no such function in SQL Server out of the box. Besides, won't this be a bit expensive, any time the column is updated? – John Saunders Apr 10 '09 at 22:34
@Jason Have you tried using Regex n database because i never heard abt this b4, this will make my task much easier – Shantanu Gupta Feb 18 '10 at 21:47
This article explains the prerequisites for getting this to work. http://www.setfocus.com/technicalarticles/clrfunctionforsqlserver_2.aspx – Jason Coyne Feb 19 '10 at 21:29

score 2 · Answer 2 · answered Jun 05 '09 at 16:57

2

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

answered Jun 05 '09 at 16:57

score 1 · Answer 3 · answered Apr 10 '09 at 23:13

1

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.

answered Apr 10 '09 at 23:13

tpdi

34,554
11
80
120

score 0 · Answer 4 · edited May 23 '17 at 12:11

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...

Creating column with user defined constraint

4 Answers4