Check if a variable contains any non-numeric digits in SQL Server

Question

I've a query like below:-

DECLARE @rptID VARCHAR(8)
SET @rptID = (SELECT reportID FROM Reports)

In general @rptID contains numeric digits like '00001234' etc. But is there any way to validate if the variable @rptID contains any non-numeric value in it.

For ex.

IF (@rptID contains non-numeric value)
            THEN throw Error

possible duplicate of [How to detect if a string contains atleast a number?](http://stackoverflow.com/questions/2558825/how-to-detect-if-a-string-contains-atleast-a-number) — ebram khalil, May 11 '14 at 15:40

gbn · Accepted Answer · 2013-08-12T14:18:30.357

26

Check for any characters that are not in the range 0 to 9

^ is not in LIKE expressions

IF @rptID LIKE '%[^0-9]%'
   --throw error

edited Aug 12 '13 at 14:18

answered Aug 12 '13 at 13:50

gbn

422,506
82
585
676

2

Your string lacks the ending ' – eKek0 Aug 12 '13 at 14:06
Just for my own future reference: `LIKE '%[^0-9]%'` will consider blank as integer while `NOT LIKE '%[0-9]%'` will consider it as non integer. – Himanshu Aug 18 '21 at 10:22

score 0 · Answer 2 · edited Feb 05 '19 at 19:38

0

There is also an ISNUMERIC function in MSSQL if you're using version 2008 or later.

mentioned link

IF (not ISNUMERIC(@rptID) = 1)
    THEN throw Error

edited Feb 05 '19 at 19:38

Hasan

1,243
12
27

answered Feb 05 '19 at 16:08

Seana Hall

1

Check if a variable contains any non-numeric digits in SQL Server

2 Answers2

Linked