Nvarchar convert to decimal with sum

Transact-SQL
1

The column is a Varchar and this called C10 and users may put in Characters or special characters rather than a numeric or decimal amount, i.e 10.00 or 10 etc

Contents;
C.XYZ = 1000
C10 = ABC
NEW AMT = 1000

C.XYZ = 1000
C10 = ' '
NEW AMT = 1000

C.XYZ = 1000
C10 = -500
NEW AMT = 500

I don't want to exclude it if the value is a space or if a char i.e ABC

CASE ISNULL(C10,'')
WHEN '' THEN C.XYZ
ELSE
C.XYZ + CAST (C10 AS DECIMAL(14,4) )
END AS 'NEW_AMT' ,
I am getting Error converting data type nvarchar to numeric.

What do I need to include in the script

2

Easy test for numeric data:

case when c10 not like '%[^0-9.]%'
then cast(c10 as decimal(14,4)

1 Like
3

Thank you for replying

4

I am getting zero value instead of my 28100 value being substracted.
C.XYZ = 1,606,783.42
C.10 = 562,000.00
I am not getting 1,044,781.42 but getting 1,606,783.42

SUM(C.XYZ) - SUM(CAST ( ISNUMERIC(C.C10) AS DECIMAL(14,4) )) AS 'NEW_AMT' ,

5

Two problems here:

  1. ISNUMERIC is not reliable. Don't use it. See here: What's wrong with ISNUMERIC
  2. if you do use ISNUMERIC, know that it returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. You are using it as if it both tested for numeric and returns the value. It doesn't.