Error converting data type nvarchar to numeric.

ahmed salah 3,216 Reputation points
2022-06-26T09:01:35.147+00:00

I work on sql server 2019 i face issue when run statment below

UPDATE  FFFFF  
SET  ChemicalsMass= STUFF(  
(     
SELECT DISTINCT case when isnumeric(cast(cp.TotalComponentMass as decimal(18,5)))=isnumeric(cast(cp.TotalComponentMassSummation as decimal(18,5))) then cast(cp.TotalComponentMass as decimal(18,8))   when isnumeric(cast(cp.TotalComponentMass as decimal(18,12)))<>isnumeric(cast(cp.TotalComponentMassSummation as decimal(18,12))) then cast(cp.TotalComponentMass + '|'+ cp.TotalComponentMassSummation as varchar(600))  end   
--SELECT DISTINCT '|' + CAST(ISNULL(REPLACE(acc.Name,'|','/') ,'NULL') AS NVARCHAR(50))  
FROM ExtractReports.dbo.ChemicalConfilict FF   
INNER JOIN Parts.ChemicalMaster Ptt WITH(NOLOCK) ON FF.ChemicalID=Ptt.ChemicalID AND Ptt.ChemicalID=FFFFF.ChemicalID  
inner join  parts.chemicalprofiles cp with(nolock) on cp.chemicalid=ptt.chemicalid  

WHERE ff.ChemicalID=FFFFF.ChemicalID  
        FOR XML PATH ('')  

),1,0,'')  
FROM  ExtractReports.dbo.ChemicalConfilict FFFFF  

after run statment above i get error

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

totalcomponentmass
190976.89922
TotalComponentMassSummation
28279.27133

so how to solve this issue please ?

Expected result will be

190976.89922 | 28279.27133

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,423 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,688 questions
0 comments No comments
{count} votes

Accepted answer
  1. Bert Zhou-msft 3,436 Reputation points
    2022-06-27T03:08:55.697+00:00

    Hi,@ahmed salah

    It seems that you have mentioned this issue before, just following Erland's words , I think the isnumeric function does not work here , are you comparing the precision of these two fields is poor, I have other ideas for the separator, I hope it can give you inspiration .

    declare @var1 VARCHAR(MAX) =190976.89922  
    declare @var2 VARCHAR(MAX) =28279.27133  
    select result=concat_WS('|',@var1,@VAR2)---NEW VERSION  
    SELECT STUFF(concat('|' + @var1,'|' +@VAR2),1,1,'')AS result---ODD VERSION  
    

    Best regards,
    Bert Zhou


1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 116.4K Reputation points MVP
    2022-06-26T09:28:46.25+00:00

    You have

       case when isnumeric(cast(cp.TotalComponentMass as decimal(18,5))) =   
                 isnumeric(cast(cp.TotalComponentMassSummation as decimal(18,5)))      
          then cast(cp.TotalComponentMass as decimal(18,8))  
    

    That is if both columns passes the isnumeric check, or if none of them do, cast the first column do decimal.

    Please don't tell me that you are surprised that this leads to a conversion error.

    Since I don't know what you want to achieve, I can't say how you should write this, but you first need to figure what logic you are looking for.

    But some hints:

    • isnumeric is useless. It tells you if the string can be converted to any numeric data type. isnumeric('1E5') returns 1, but you cannot convert that to decimal. Use try_cast() IS NOT NULL to deduce this instead.
    • Instead of the bulk FOR XML PATH, use the simpler string_agg.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.