Convert string number to decimal

Transact-SQL
1

I have several columns that are of a string format that are actual numbers but some of them are empty. can someone tell me how to convert them to text but I get an error? here is my sample:

Select  
     MES_MACHINE 
    ,[TubeJobNo],LEFT(Customer_Name,25)AS Customer
    ,Sales_Order_Line_No AS OrderNo
    ,[AFL_O2MES_SO_DJ].[Child_Job_Number],CAST(Setup AS decimal)--- that worked,ISNULL(FORMAT([FinalDataOp Completion],'N0'),'')AS FinalDataOpCompletion --- that does not work, how do you convet this?

,ISNULL(FORMAT([JobFinishedWIP Issue Fiber Set]*FibersCount,'N0'),'')AS TotalLengthConsumed

2

Here is the error I am getting: Msg 8114, Level 16, State 5, Line 18
Error converting data type nvarchar to numeric.

4

please show us sample table and some sample data

create table #meow_Cat265(Setup  nvarchar(50), [FinalDataOp Completion] tinyint )

insert into #meow_Cat265
select 21575115 as MES_MACHINE, 21575115 as [FinalDataOp Completion] union
select 112719454 as MES_MACHINE, 112719454 as [FinalDataOp Completion] union
select 117575457 as MES_MACHINE, 117575457 as [FinalDataOp Completion] union
select 125243501 as MES_MACHINE, 125243501 as [FinalDataOp Completion] union
select 149575571 as MES_MACHINE, 149575571 as [FinalDataOp Completion] union
select 176719682 as MES_MACHINE, 176719682 as [FinalDataOp Completion] union
select 180195692 as MES_MACHINE, 180195692 as [FinalDataOp Completion] union
select 181575685 as MES_MACHINE, 181575685 as [FinalDataOp Completion] union
select 224719853 as MES_MACHINE, 224719853 as [FinalDataOp Completion] union
select 245575913 as MES_MACHINE, 245575913 as [FinalDataOp Completion] 
select 'no245575913' as MES_MACHINE, 245575913 as [FinalDataOp Completion] union

--select 'select ' + cast(object_id as varchar(50)) + ' as MES_MACHINE, ' 
--+  cast(object_id as varchar(50)) + ' as [FinalDataOp Completion] union'  
--from sys.tables


select CAST(Setup as decimal), ISNULL(FORMAT([FinalDataOp Completion],'N0'),'')AS FinalDataOpCompletion
 From #meow_Cat265

drop table #meow_Cat265
5

thank you. I ended up using Try_CONVERT function:
,TRY_CONVERT (int,Primary_Qty) As [TargetLength]
,ISNULL(TRY_CONVERT (int, [FinalDataOp Completion]),'') AS FinalDataOpCompletion
,ISNULL(TRY_CONVERT (int,[JobFinishedWIP Issue Fiber Set]*FibersCount),'') AS TotalLengthConsumed