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

Question

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='@')))

Answer 1

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