using ISNUMERIC() with sql_variant SQL Server

Question

How can I use ISNUMERIC() function with sql_variant datatype?

This following code doesn't work

DECLARE @x sql_variant 
SET @x = 3 
IF ISNUMERIC(@x) = 1 
 SELECT 'Numeric'
 ELSE
SELECT ' NOT'

Error :

Msg 8116, Level 16, State 1, Line 4
Argument data type sql_variant is invalid for argument 1 of isnumeric function.

But this works

IF ISNUMERIC(CAST(@x AS INT)) = 1

is there is any way of making this happened with out CAST()

Haven't found any thing useful on google regarding this issue thanks , I'm using SQL Server 2008 R2

Are you trying to check if the value is numeric, or if the underlying datatype is a numeric type? For example, should the NVARCHAR '3' be deemed numeric? — Joachim Isaksson
– Joachim Isaksson, Commented Apr 24, 2013 at 18:23
well Filip Answer works with the situation i'm having now but it still doesn't make sense this to call this @x = '123' Numeric right ? — Mina Gabriel
– Mina Gabriel, Commented Apr 24, 2013 at 18:26
watch out, ISNUMERIC() doesn't always "work" as you expect it to. see: simple-talk.com/blogs/2011/01/13/… — KM.
– KM., Commented Apr 24, 2013 at 18:53

Filip · Accepted Answer · 2013-04-24 18:14:51Z

4

Casting to int doesn't really make sense. Your conversion will fail (when something else than an integer appears) and you'll get an error.

You can, however, cast to nvarchar(max) - this should always work and your ISNUMERIC function will behave as expected

answered Apr 24, 2013 at 18:14

Filip

2,3591 gold badge19 silver badges25 bronze badges

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

Comments

Joachim Isaksson · Accepted Answer · 2013-04-24 18:49:17Z

3

If you want to know if the underlying datatype is numeric, you can use SQL_Variant_Property to find out;

DECLARE @x sql_variant 
SET @x = 3.2

SELECT CASE 
       WHEN SQL_Variant_Property(@x, 'Precision') = 0 
       THEN 'NO' 
       ELSE 'YES' 
       END

answered Apr 24, 2013 at 18:49

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Collectives™ on Stack Overflow

using ISNUMERIC() with sql_variant SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related