Convert string number to decimal

Question

question

samn265 asked Feb 27, '19

Convert string number to decimal

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

sql-server-2012

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

samn265 commented · Feb 27, 2019 at 05:41 AM

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

0 ·

Answer 1 · 2019-02-27T09:57:28Z

WRBI answered Feb 27, '19

Use the TRY_CONVERT() function. It will convert everything that it can convert and then return NULL for everything else. You can then gracefully handled the NULL or clean columns that are not meeting your data requirements.

https://docs.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql

The best method is have a column with the correct data type from the start and only populate with the correct data, if you can alter your data model I'd do that!

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2019-02-27T20:52:07Z

samn265 answered Feb 27, '19

Thank you. I ended up using Try_CONVERT Function.

Here is the sample code:

,TRY_CONVERT (int,[StagingDB].[dbo].[AFL_O2MES_TubeJob].Primary_Qty) As [TargetLength]
,ISNULL(TRY_CONVERT (int, [FinalDataOp Completion]),'') AS FinalDataOpCompletion
,ISNULL(TRY_CONVERT (int,[JobFinishedWIP Issue Fiber Set]*FibersCount),'') AS TotalLengthConsumed

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question