Err 8114 converting data type nvarchar to numeric

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Err 8114 converting data type nvarchar to numeric

Author

Topic

phenreid
Starting Member

29 Posts

Posted - 2014-07-05 : 21:43:09

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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-07 : 16:02:46

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

phenreid
Starting Member

29 Posts

Posted - 2014-07-08 : 01:16:33

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?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-08 : 12:24:47

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.

phenreid
Starting Member

29 Posts

Posted - 2014-07-08 : 14:00:35

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

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-07-08 : 17:04:29

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources