Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Formating Numbers via T/SQL

User-Defined Functions

Formating Numbers via T/SQL

by  mrdenny  Posted    (Edited  )
Often I've been requested to write a SQL Script that formats the numbers into a way that is easy for people to read. Complete with commas, and decimals. Since SQL doesn't have a way to do this internally, I came up with this little function. When calling the function there are two parameters that need to be passed. The first is the number you want to convert. The second is weather or not you want to display decimals.

The default number of decimal places to show is 2. This can be changed in the second line as needed. To hid decimals set the @ShowDecimal parameter to 0. To show them, set it to 1.

To call the function you can use it like this:
Code: 
select name, dbo.FormatNumber(id, 0)
from master.dbo.sysobjects

Below is the actual function.
Code: 
CREATE Function FormatNumber 
(@RawNumber numeric(32,2) = 0,
	@ShowDecimal as bit = 0
)
returns varchar(200) AS
BEGIN
	declare @FormattedNumber as varchar(100)
	declare @AfterDecimal as varchar(100)
	declare @i as int
	declare @j as int
	set @i = 0
	set @j = 0
	set @FormattedNumber = convert(bigint, @RawNumber)
	set @AfterDecimal = @RawNumber
	if charindex('.', @AfterDecimal) <> 0
	BEGIN
		set @AfterDecimal = '.' + right(@AfterDecimal, len(@AfterDecimal)-charindex('.', @AfterDecimal))
	END
	ELSE
	BEGIN
		set @AfterDecimal = ''
	END
	
	
	while @i <> len(@FormattedNumber)
	BEGIN
		if @j = 3
		BEGIN
			set @j = -1
			set @FormattedNumber = left(@FormattedNumber, len(@FormattedNumber)-@i) + ',' + right(@FormattedNumber, @i)
		END
		set @j = @j + 1
		set @i = @i + 1
	END
	if @ShowDecimal <> 0
		set @FormattedNumber =  @FormattedNumber + @AfterDecimal
	return @FormattedNumber
END
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top