CODEWITHSUNDEEP
sqlsql-serverssmscheck-constraint
Data Engineer
Data Engineer

Reputation: 827

SQL CHECK CONSTRAINT to reject alphanumeric and special charters and allow only numeric from 0 to 9

I have the following constraint that is supposed to allow only digits from 0 to 9 insertion and not any special and alpha characters. But that is not the case, for example when using this update statement: 

   update MyDB.dbo.MyTable 
    set MyTestPhoneExt = '23&'  where ID = 1;

The goal is to assure that the data being source and written to the MyTable have only digits, but the MyTestPhoneExt filed has be to VARCHAR(15) NULL.

ALTER TABLE MyDB.dbo.MyTable  WITH CHECK ADD CONSTRAINT [CHK_MyDB_MyTable _MyTestPhoneExt] 
CHECK ((MyTestPhoneExt IS NULL OR LEN(MyTestPhoneExt)>=(1) AND

LEN(MyTestPhoneExt)<=(15) AND MyTestPhoneExt LIKE '%[0-9]%' 

--OR len(MyTestPhoneExt)>=(1) AND len(MyTestPhoneExt)<=(15) 

AND NOT MyTestPhoneExt LIKE '%[a-zA-Z]%' 
AND NOT (MyTestPhoneExt=' ' OR MyTestPhoneExt='' OR MyTestPhoneExt='&' OR

MyTestPhoneExt='`' OR MyTestPhoneExt='~' OR MyTestPhoneExt='>' OR 

MyTestPhoneExt='<' OR MyTestPhoneExt='.' OR MyTestPhoneExt=',' OR 

MyTestPhoneExt=';' OR MyTestPhoneExt=':' OR MyTestPhoneExt='?' OR 

MyTestPhoneExt='_' OR MyTestPhoneExt='=' OR MyTestPhoneExt='+' OR

MyTestPhoneExt='!' OR MyTestPhoneExt='@' OR MyTestPhoneExt='#' OR

MyTestPhoneExt='%' OR MyTestPhoneExt='$' OR MyTestPhoneExt='^' OR

MyTestPhoneExt='*' OR MyTestPhoneExt=',' OR MyTestPhoneExt='}' OR

MyTestPhoneExt='{' OR MyTestPhoneExt=')' OR MyTestPhoneExt='(' OR 

MyTestPhoneExt=']' OR MyTestPhoneExt='[' OR MyTestPhoneExt='|' OR 

MyTestPhoneExt='\' OR MyTestPhoneExt='/' OR MyTestPhoneExt='-' OR MyTestPhoneExt='@')))

Upvotes: 1

Views: 1747

Answers (3)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Try Using the PATINDEX inside the CHECK CONSTRAINT

CREATE TABLE Mytable
(
    MyCol NVARCHAR(50) CHECK(PATINDEX('%[^0-9]%',MyCol)=0 AND ISNUMERIC(MyCol) = 1)
)

INSERT INTO Mytable
(
    MyCol
)
VALUES(1),(2)

INSERT INTO Mytable
(
    MyCol
)
VALUES('1B'),('2A')

INSERT INTO Mytable
(
    MyCol
)
VALUES('1.0'),('2.5')


INSERT INTO Mytable
(
    MyCol
)
VALUES('1 '),('2 x')

SELECT
    *
    FROM Mytable

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

To allow only numerical values you could use TRY_CONVERT() function with check constraint 

ALTER TABLE table    
ADD CONSTRAINT CHK_MyDB_MyTable _MyTestPhoneExt 
                         CHECK(TRY_CONVERT(BIGINT, MyTestPhoneExt) IS NOT NULL)

You could also use system function ISNUMERIC() 

ALTER TABLE table    
ADD CONSTRAINT CHK_MyDB_MyTable _MyTestPhoneExt 
                             CHECK(ISNUMERIC(MyTestPhoneExt)=1)

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

Wouldn't this be a simpler way to accept only numbers?

patindex('%[^0-9]%', MyTestPhoneExt) = 0

Upvotes: 3

Related Questions