how to convert a number to string in SQL?


I search the help for T-sql, still don't know how to convert a number, for example 12345 to a string with formating like 12,345
Thanks for your help.




Join WebHost4Life.com

0
free
7/7/2006 9:38:56 PM
📁 asp.net.sql-datasource
📃 29906 articles.
⭐ 0 followers.

💬 1 Replies
👁️‍🗨️ 881 Views


The best place to do such formatting is in the front end, not at the database level.

That being said, you could use a combination of the CONVERT and CAST functions (see CAST and CONVERT) to cast the number as a money type, then convert it to a varchar, and then strip off the decimal point and 2 0's at the end:
DECLARE @test int
SELECT @test = 12345
SELECT REPLACE(CONVERT(varchar(50), CAST(@test AS money), 1),'.00','')

Or, you could make use of a general-purpose UDF like this one from Novick Software's newsletter, which gives you a lot more flexibility and the code is reusable: Formatting a Number
 

Terri Morton
Engagement Manager, Neudesic

How to ask a question

0
tmorton
7/8/2006 3:23:10 PM
Reply: