0

I have complex query where i am having a varchar variable. I need to perform various operation on it based on the datatype of data i receive in that variable.

If varchar variable contains numeric value then i need to convert it to numeric datatype so i can perform > < operation on it.

It all works fine except below part

    Declare @testVal as varchar(50)='ok'
select CASE WHEN IsNUMERIC(@testVal)=1 THEN Cast(@testVal as numeric) ELSE 'n' End

Above query gives me error "Error converting data type varchar to numeric."

I can't use try_convert and advanced functions like that as i am using older version of SQL.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Ketan Vaghasiya
  • 384
  • 4
  • 13
  • 1
    *"I can't use try_convert and advanced functions like that as i am using older version of SQL."* - It would be useful to know which version of SQL Server you're using then – Diado Oct 17 '18 at 08:02
  • Using 2008 R2. And i have tried that answer with ISNUMERIC and its throwing error. – Ketan Vaghasiya Oct 17 '18 at 08:06
  • I have provided the sample query and error both in the question itself. – Ketan Vaghasiya Oct 17 '18 at 08:24
  • @Diado If varchar variable contains numeric value then i need to convert it to numeric datatype so i can perform > < operation on it. – Ketan Vaghasiya Oct 17 '18 at 08:29
  • 1
    You're using ISNUMERIC as suggested in dup question but nature of problem is different. – Salman A Oct 17 '18 at 08:30
  • Wait a second, you're returning a varchar if it's not numeric? You need to return the same type from all branches of the case statement – Diado Oct 17 '18 at 08:33
  • The problem is with the `'n'` part. ***THAT*** is what is causing the error. Change it to NULL or remove the else part altogether. – Salman A Oct 17 '18 at 08:34
  • @Diado will that be a problem to return different Data Type? – Ketan Vaghasiya Oct 17 '18 at 08:35
  • Yes. As @SalmanA suggested, return `NULL` if it's not, otherwise SQL server will be unable to determine whether to treat the value as numeric or varchar. – Diado Oct 17 '18 at 08:36
  • 1
    `ISNUMERIC` is the most misleading function you'll ever encounter. It tells you that a string can be converted to **any** of the numeric data types, when surely what everyone cares about is whether it can be converted to a *specific* data type. `ISNUMERIC('£')` returns `1`. `CONVERT(numeric,'£')` returns the same error (it *can* be converted to `money`). It's usually better to pick which characters are legal and use some form of `like` expression. – Damien_The_Unbeliever Oct 17 '18 at 08:57

2 Answers2

2

The CASE statement tries to consolidate the datatypes from all branches into one according to datatype precedence. In your example it is attempting to convert the varchar literal 'n' to numeric hence the error converting data type. Remove the ELSE branch altogether:

CASE WHEN ISNUMERIC(@testVal) = 1 THEN CAST(@testVal AS NUMERIC) END

Some examples:

SELECT val, CASE WHEN ISNUMERIC(val) = 1 THEN CAST(val AS NUMERIC) END AS to_num
FROM (VALUES
    ('1'),
    (NULL),
    ('ok')
) AS v(val)

Result:

val  | to_num
-----+-------
1    | 1
NULL | NULL
ok   | NULL

You mentioned that you want to compare the numeric value using <>. NULL should have not problem with that, it simply won't match.

Salman A
  • 262,204
  • 82
  • 430
  • 521
0

This seems to work perfectly with sql_variant

select CASE WHEN IsNUMERIC(@testVal)=1 THEN Cast(@testVal as numeric) ELSE Cast(@testVal as sql_variant) End
Ketan Vaghasiya
  • 384
  • 4
  • 13