How to convert from nchar to decimal in SQL?

Question

I have 2 tables(source and destination) which are respectively NOR_LABOR and ALL_LABOR_DETAILS. In the source table(NOR_LABOR) there is a column feet_produced with the data type nchar(10). In the destination table(ALL_LABOR_DETAILS) there’s a column labor_feet_produced with the data type decimal(18,4). I want to convert the feet_produced from nchar(10) to decimal(18,4) and paste it in the ALL_LABOR_DETAILS table’s labor_feet_produced column.

I have found a code regarding a similar issue but did not do the exact as I need to do, following is that code snippet :

Select feet_produced AS feet_produced_s, CASE WHEN Isnumeric(feet_produced) = 1
THEN CONVERT(DECIMAL(18,2),feet_produced) 
ELSE 0 END AS feet_produced
INTO [MES_DEV].[dbo].[ALL_LABOR_DETAILS]
from [dbo].[NOR_LABOR]

Asked2021-05-10byMegaTrone

Answer

There are values that will test true for IS_NUMERIC(), but will fail to convert to decimal.

Instead, use TRY_CONVERT() which will return either the successfully-converted-to-decimal value, or a NULL when it fails. (You can then COALESCE to zero to get your desired result).

Here is a short example set of values, using TRY_CONVERT():

    SELECT 
            TryConvert = COALESCE(TRY_CONVERT(decimal(18,4),TestValues),0)
    FROM (
            VALUES('10.6'),
                  ('ten'),
                  ('7d2'),
                  ('10000000000'),
                  ('10.00000001')
            ) AS x(TestValues);

The same set of values using your example code will throw an error:

    SELECT 
            IsNumericCase = CASE 
                                WHEN Isnumeric(TestValues) = 1
                                    THEN CONVERT(DECIMAL(18,2),TestValues) 
                                ELSE 0 
                            END
    FROM (
            VALUES('10.6'),
                  ('ten'),
                  ('7d2'),
                  ('10000000000'),
                  ('10.00000001')
            ) AS x(TestValues);

This error is returned because 7d2 is numeric, but cannot be converted to decimal.

Msg 8114, Level 16, State 5, Line 14 Error converting data type varchar to numeric.

Answered2021-05-10byAndy Mallon