ddf_FormatNumeric - formats a dollar value

This is a function that will take a numeric amount and format it as text, adding (optionally) a currency symbol and commas or the text 'hr' after the number; again with commas.

This screen shot shows how to call it with the optional dollar/hour parameter

 

Editors note:

Do you have a cool function that you use all the time? Why not post it, and let us put it in the SQL Menu along with our library?

Related Articles

... and you 'll find more on the SQL (General) Menu

IF EXISTS (SELECT *
       FROM   sysobjects
       WHERE  name = N'ddf_FormatNumeric')
    DROP FUNCTION ddf_FormatNumeric
GO
  
  
CREATE FUNCTION ddf_FormatNumeric (@amount numeric(19,2), @type int)
-- highlight and run this line to test the function
--select dbo.ddf_FormatNumeric(12.99,1)
--select dbo.ddf_FormatNumeric(123.99,1)
--select dbo.ddf_FormatNumeric(1234.99,1)
--select dbo.ddf_FormatNumeric(12345.99,1)
--select dbo.ddf_FormatNumeric(123456.99,1)
--select dbo.ddf_FormatNumeric(1234567.99,1)
--select dbo.ddf_FormatNumeric(12345678.99,1)
--select dbo.ddf_FormatNumeric(123456789.99,1)
  
Returns varchar(20)
  
AS
begin
declare @out as varchar(20)
declare @pos int
 
if @type = 1 begin --dollar
    set @out = '$' + convert(varchar(20),@amount)
    if len(@out) > 7 begin
        set @pos = len(@out) - 6
        set @out = substring(@out,1,@pos) + ',' + right(@out,6)
    end
 
    if len(@out) > 11 begin
        set @pos = len(@out) - 10
        set @out = substring(@out,1,@pos) + ',' + right(@out,10)
    end
end
 
--select dbo.ddf_FormatNumeric(123.99,2)
--select dbo.ddf_FormatNumeric(1234.99,2)
--select dbo.ddf_FormatNumeric(12345.99,2)
--select dbo.ddf_FormatNumeric(123456.99,2)
--select dbo.ddf_FormatNumeric(1234567.99,2)
--select dbo.ddf_FormatNumeric(12345678.99,2)
--select dbo.ddf_FormatNumeric(123456789.99,2)
if @type = 2 begin --hour
    set @out = convert(varchar(20),@amount) + ' hr'
 
    if len(@out) > 9 begin
        set @pos = len(@out) - 9
        set @out = substring(@out,1,@pos) + ',' + right(@out,9)
    end
 
    if len(@out) > 13 begin
        set @pos = len(@out) - 13
        set @out = substring(@out,1,@pos) + ',' + right(@out,13)
    end
     
end
 
return (@out)
  
end

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences