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? — ebram khalil
– ebram khalil, Commented May 11, 2014 at 15:40

gbn · Accepted Answer · 2013-08-12 14:18:30Z

30

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, 2013 at 14:18

answered Aug 12, 2013 at 13:50

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

eKek0 Over a year ago

Your string lacks the ending '

Himanshu Over a year ago

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.

Hasan · Accepted Answer · 2019-02-05 19:38:35Z

1

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 5, 2019 at 19:38

Hasan

1,30813 silver badges27 bronze badges

answered Feb 5, 2019 at 16:08

Seana Hall

111 bronze badge

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related