CODEWITHSUNDEEP
sqlsql-serversql-server-2008casting
Farbkreis
Farbkreis

Reputation: 644

MSSQL Server 2008 - Convert Nvarchar to numeric

im using mssql 2008 and Im permanently failing to convert an nvarchar to numeric values.

Can you please advise? I have different solutions I found over the www, but all of them are failing with the error message:

Msg 8114, Level 16, State 5, Line 15 Error converting data type nvarchar to numeric.

I have built an reduced example for demonstration purpose:

IF OBJECT_ID('tempdb..#temptable', 'U') IS NOT NULL 
DROP TABLE dbo.#temptable 

create table #temptable(
col1 nvarchar(10),
col2 numeric(10,5)
)
insert into #temptable values ('0,5','0')

select *,convert(numeric(18,2),col1)   from #temptable

 UPDATE #temptable
 SET col2 = CAST(col1 AS numeric(10,5))
 WHERE ISNUMERIC(col1) = 1


SELECT col1
, CASE ISNUMERIC(col1)
WHEN    1 THEN  CONVERT(numeric(18,2),col1)  
ELSE 0.00
END

from #temptable

Upvotes: 0

Views: 2145

Answers (3)

Farbkreis
Farbkreis

Reputation: 644

I alreay found an strong hint whats going wrong... the issue seems to be related to the , as seperator while the SQL server expects an .

if you change the following line to:

insert into #temptable values ('0.5','0')

its working

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269883

Use try_convert() in SQL Server 2012+:

UPDATE #temptable
   SET col2 = TRY_CONVERT(numeric(10,5), col1)
   WHERE ISNUMERIC(col1) = 1;

SQL Server re-arranges expression valuation in a query. So, the CAST() might be implemented before the WHERE -- resulting in the error. You can make a similar change to the SELECT version of your query.

In SQL Server 2008, you should be able to do effectively the same thing using CASE:

UPDATE #temptable
   SET col2 = (CASE WHEN ISNUMERIC(col1) = 1 THEN CONVERT(numeric(10, 5), col1) END)
   WHERE ISNUMERIC(col1) = 1;

Note: There may be cases where ISNUMERIC() returns "1", but the value cannot be converted (for instance, overflow). In that case, this version would still fail.

Upvotes: 0

S3S
S3S

Reputation: 25112

The problem is you are using ISNUMERIC(col1) = 1 which fails for a ton of cases like ISNUMERIC('1e4') or ISNUMERIC('$') or in your case, ISNUMERIC('1,000,000'). Don't use ISNUMERIC in this fashion.

Instead, try this...

 UPDATE #temptable
 SET col2 = CAST(col1 AS numeric(10,5))
 WHERE col1 not like '%[^0-9.]%'

Upvotes: 0

Related Questions