How to Convert Nvarchar to Numeric in SQL Server

Convert nvarchar to numeric in MSSQL Server

Your data would appear to have values are not valid numeric values. Use try_convert():

select try_convert(numeric(38, 12), col)

This will return NULL if there is a failure in the conversion.

You can find the values that fail the conversion by doing:

select col
from t
where try_convert(numeric(38, 12), col) is null and col is not null;

You need to use try_convert() wherever you reference the column as a numeric. Converting in the select only applies to the select.

SQL Server: Error converting data type nvarchar to numeric

You might need to revise the data in the column, but anyway you can do one of the following:-

1- check if it is numeric then convert it else put another value like 0

Select COLUMNA AS COLUMNA_s, CASE WHEN Isnumeric(COLUMNA) = 1
THEN CONVERT(DECIMAL(18,2),COLUMNA) 
ELSE 0 END AS COLUMNA

2- select only numeric values from the column

SELECT COLUMNA AS COLUMNA_s ,CONVERT(DECIMAL(18,2),COLUMNA) AS COLUMNA
where Isnumeric(COLUMNA) = 1

SQL error convert nvarchar to numeric in a view

Replace comma (,) with empty and then convert to numeric (assuming this column will always have only numeric values).

select id, totalsales as totalsales_nvarchar
 ,cast(replace(totalsales, ',','') as numeric) AS totalsales_numeric 
from tb1

Error converting data type nvarchar to numeric. Tried convert and replace

You're not handling the blank case, this works with test data:

select sum(cast(case when replace(replace(ZFREEGOOD,',',''),' ','') = '' then '0' else replace(replace(ZFREEGOOD,',',''),' ','') end as decimal(22,8))) from TEMP_GBR_History_1611

Error on converting data type nvarchar to numeric

You could shred the XML values as nvarchar(20) and then use try_convert() to convert them to numeric(18,0) or null...

create table MobileUnit
(
    IDMobileUnit int identity  primary key,
    Type numeric null,
    Length numeric null,
    Width numeric null,
    Height numeric null
);

declare @xml xml =
'<NIACList>
 <NIAC>
  <CommercialUnit>
   <MobileUnit>
    <Type  />
    <Length  />
    <Width  />
    <Height  />
   </MobileUnit>
   <MobileUnit>
    <Type>1</Type>
    <Length>2</Length>
    <Width>3</Width>
    <Height>4</Height>
   </MobileUnit>
  </CommercialUnit>
 </NIAC>
</NIACList>';

INSERT INTO MobileUnit (Type, Length, Width, Height)
    SELECT 
        [Type] = try_convert(numeric(18, 0), c.value('Type[1]', 'nvarchar(20)')),
        Length = try_convert(numeric(18, 0), c.value('Length[1]', 'nvarchar(20)')),
        Width = try_convert(numeric(18, 0), c.value('Width[1]', 'nvarchar(20)')),
        Height = try_convert(numeric(18, 0), c.value('Height[1]', 'nvarchar(20)'))
    FROM 
        @xml.nodes('/NIACList/NIAC/CommercialUnit/MobileUnit') MobileUnit(c);

select * from MobileUnit;

Which yields...

IDMobileUnit  Type  Length  Width  Height
           1  null    null   null    null
           2     1       2      3       4

---

Related Topics