SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Err 8114 converting data type nvarchar to numeric
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

phenreid
Starting Member

29 Posts

Posted - 07/05/2014 :  21:43:09  Show Profile  Reply with Quote
I am capturing unstructured date such that value field can be numeric or text. Some of the data has Chinese characters, hence the need for nvarchar.

This query fails with error 8114

select *
from results
where (field like '%abc%' or field like '%def%')
and convert(NUMERIC(10,2),value)>2

None of the popular suggestions about ISNUMERIC() case statements or derived tables work because of the order in which the query processor evaluates this query.

From another post, I can fool the plan, so this works, but isn't elegant.

SELECT
ID,
field,
Value,
x.NumericValue
FROM results
CROSS APPLY (
SELECT [NumericValue] = CASE
WHEN ISNUMERIC(Value) = 1 THEN convert(NUMERIC(10,2),value)
ELSE 0 END
) x
WHERE NumericValue > 2

If I insert all the subset of data into a temptable and query on that, it works, but that isn't a desired workaround.

Note that select convert(int,value) from results works fine, the convert error only comes up on the where clause.

What's the most elegant solution?

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/07/2014 :  16:02:46  Show Profile  Reply with Quote
What kind of numeric values do you have? Are they decimal values (with a decimal point) or are they integers? If they are Integer values, they you can use a LIKE comparison:
DECLARE @Foo TABLE (Val VARCHAR(50))

INSERT @Foo (Val)
VALUES
('1')
,('1,000')
,('5e3')
,('100')
,('100.00')
,('9781297')
,('9781e297')
,('978w1297')
,('asdfg')
,('.9781297')
,('12d3')
,('$123,456.00')
,(' 12 ')
,(char(10))
,('$')
,(NULL)

SELECT 
    Val
    ,ISNUMERIC(Val) AS IsValNumeric
    ,CASE WHEN Val NOT LIKE '%[^0-9]%' THEN 1 ELSE 0 END AS IsInt
FROM @Foo
Go to Top of Page

phenreid
Starting Member

29 Posts

Posted - 07/08/2014 :  01:16:33  Show Profile  Reply with Quote
Thank you. No I had already tried that solution. Again, everything is fine in the select list. But when I put the criteria in a where clause that's when I get the conversion error. My data is sometimes text like pass/fail, other times it's integer and some are decimal.

When I try this statement:

select * from results where (field like '%abc%' or field like '%def%') and convert(int,value)>2
and case when value not like '%[^0-9]%' THEN 1 else 0 end = 1

I get this error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '116.5' to data type int.

I tried switching the field to VARCHAR, but I get the same error. The problem is that the optimizer wants to evaluate the convert function before the rest of the criteria in the and and nothing simple or obvious seems to change that.

Using your example is perfect. Revising your example, this statement causes the error I'm trying to work around:

select * from @foo
where CASE WHEN Val NOT LIKE '%[^0-9]%' THEN 1 ELSE 0 END = 1 and val >2

Yields:
Msg 245, Level 16, State 1, Line 22
Conversion failed when converting the varchar value '1,000' to data type int.

Maybe I need a view or UDF that somehow sub-selects the numeric data, but what a pain that would be. There must be some way to fool the optimizer, but what is it?



Edited by - phenreid on 07/08/2014 01:25:41
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/08/2014 :  12:24:47  Show Profile  Reply with Quote
Yeah, you are in a tough spot. You can control the order of operations to a small degree by using a case expression (or nested case expressions); Which might work here. Otherwise, I'd suggest that you might be able to use a temp table an try to filter in your insert statement the best you can and then run your query against the temp table.
Go to Top of Page

phenreid
Starting Member

29 Posts

Posted - 07/08/2014 :  14:00:35  Show Profile  Reply with Quote
I worked around by creating a view:

SELECT Field,
CASE
WHEN ISNUMERIC(Value) = 1 THEN convert(FLOAT,value)
ELSE 0 END as Value
From results

Then it works when I query on the view:

select *
from results_numeric_vw
where (field like '%abc%' or field like '%def%') and value > 2


Edited by - phenreid on 07/08/2014 14:17:27
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
409 Posts

Posted - 07/08/2014 :  17:04:29  Show Profile  Reply with Quote
You could add a computed column to the table, then use that in the WHERE clause:

ALTER TABLE results
ADD value_numeric AS CASE WHEN ISNUMERIC(value) = 1 THEN value ELSE 0 END

...
where (field like '%abc%' or field like '%def%')
and value_numeric > 2
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000