To_Char and T_Number alternate in SQL Server for formatting

Question

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?

Solution

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.

Comments

[Zerotoinfinity]

I don't think I can use CAST([Column] AS Char(-987654321)) in SQL Server.

[Crono]

@Zerotoinfinite Indeed you can't. Casting and formatting in SQL Server doesn't come together.