Menu
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
989
This is the way: SELECT * FROM TABLE_NAME WHERE NOT REGEXP_LIKE(COLUMN_NAME, '^-?[0-9.]+$ '); This also excludes values which contain decimals.
SQL Server has an ISNUMERIC() function that returns 1 for numeric values and 0 for non-numeric values.
The ISNUMERIC() function tests whether an expression is numeric. This function returns 1 if the expression is numeric, otherwise it returns 0.
As defined in the official Microsoft SQL Server documentation, the ISNUMERIC function determines whether an expression is a valid numeric type. It is a scalar function that takes a string expression as a parameter and returns an integer.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
