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

score 2 · Answer 1 · answered Mar 09 '18 at 04:23

2

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

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

answered Mar 09 '18 at 04:23

Dan Bracuk

20,699
4
26
43

Jayasurya Satheesh · Accepted Answer · 2018-03-09T05:43:19.697

2

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

edited Mar 09 '18 at 05:43

answered Mar 09 '18 at 04:24

Jayasurya Satheesh

7,826
3
22
39

isnumeric('1,000') returns 1 – Dan Bracuk Mar 09 '18 at 04:27
Satheesh, the suggested solution looks good, but ' ' space is excepted. – Data Engineer Mar 09 '18 at 05:22
INSERT INTO dbo.Mytable ( MyCol ) VALUES('01 ') – Data Engineer Mar 09 '18 at 05:23
I've made some more changes in the Answer and seems like it's working as expected now – Jayasurya Satheesh Mar 09 '18 at 05:33
Satheesh, just one more thing address it that latest changes allow empty string to be inserted into the field. And this should not be the case – Data Engineer Mar 09 '18 at 05:41
I've added ISNUMERIC check also. – Jayasurya Satheesh Mar 09 '18 at 05:43
It did resolved the empty string issue, but another appeared. The died should be nullable, but not it does not except NULL – Data Engineer Mar 09 '18 at 05:46
I fixed the NULL insertion issue. the final version of the constraint should look like: – Data Engineer Mar 09 '18 at 05:48
CHECK(MyCol IS NULL OR PATINDEX('%[^0-9]%',MyCol)=0 AND ISNUMERIC(MyCol) = 1) – Data Engineer Mar 09 '18 at 05:49

Yogesh Sharma · Answer 3 · 2018-03-09T04:23:33.653

0

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)

edited Mar 09 '18 at 04:23

answered Mar 09 '18 at 04:15

Yogesh Sharma

49,870
5
26
52

Yogesh, unfortunately, any NUMERIC data types are not an option as some of the records start with 0 – Data Engineer Mar 09 '18 at 04:27
Both tryconvert and isnumeric are fine for leading zeros. isnumeric will return 1 on '1,000' – Dan Bracuk Mar 09 '18 at 04:29

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

3 Answers3