To_Char and T_Number alternate in SQL Server for formatting
What is the SQL Server alternate of
To_CHAR([Column_Name], -987654321)
To_Number([Column_Name],'999999999D99999900')
? Also, What I can predict from the syntax is that they both are being used for formatting. Then what is the difference between them?
Answer
Casting in Sql Server is done like this:
CAST([column] AS CHAR([required length]))
CAST([column] AS [whatever numeric type you want])
Afterwards you can use the FORMAT function if you are using Sql Server 2012 or higher.
FORMAT([value], [format])
In your case, I would try this:
FORMAT(CAST([column] AS CHAR([required length])), '-987654321')
FORMAT(CAST([column] AS [whatever numeric type you want]), '000000000.000000##')
Although I don't quite understand what your formatting strategy is here, especially for your CHAR casting... :)
Prior to Sql Server 2012 formatting values is awkward, at best. The good news is that if you know some C# you could expose the powerful .NET Framework formatting mechanism as a Sql User Defined Function mapped to a assembly running on SQLCLR. From there you could very easily make an equivalent of the SQL2012 FORMAT function. This will work with Sql Server 2005 and 2008.