CREATE FUNCTION DBO.FORMATNUMBER (@VALUE NUMERIC(19,6)) 
RETURNS VARCHAR(100) 
AS 
BEGIN 
DECLARE @PART1 VARCHAR(100) -- HOLDS ALL THE DIGITS ON THE LEFT OF THE DICMAL PLACE
DECLARE @PART2 VARCHAR(100) -- GOLD ALL DIGITS ON THE RIGHT SIDE OF THE DECIMAL PLACE
DECLARE @HOLDER VARCHAR(100) -- HOLD THE MAIN NUMBER
DECLARE @COUNTER INT -- COUNTER FOR THE WHILE LOOP 
DECLARE @FLG INT -- INDICATOR FOR POSITIVE/NEGATIVE NUMBERS

SET @FLG = 0

IF @VALUE < 0 
BEGIN
SET @FLG = 1
END

SET @HOLDER = (CONVERT(VARCHAR(100),ABS(@VALUE))) 

SET @PART1 = SUBSTRING(@HOLDER, 1, CHARINDEX('.', @HOLDER)-1) 
SET @PART1 = REVERSE(@PART1) 
SET @PART2 = SUBSTRING(@HOLDER, CHARINDEX('.', @HOLDER),LEN(@HOLDER)-CHARINDEX('.', @HOLDER)+1) 

SET @COUNTER = 1 
SET @HOLDER = '' 

WHILE @COUNTER <= LEN(@PART1) 
BEGIN 
IF @COUNTER%3 = 0 
BEGIN 
SELECT @HOLDER = @HOLDER + SUBSTRING(@PART1, @COUNTER, 1)+',' 
END 
ELSE 
BEGIN 
SELECT @HOLDER = @HOLDER + SUBSTRING(@PART1, @COUNTER, 1) 
END 
SET @COUNTER = @COUNTER + 1 
END 

IF SUBSTRING(REVERSE(@HOLDER), 1, 1) = ',' BEGIN SET @HOLDER = SUBSTRING(@HOLDER, 1, LEN(@HOLDER)-1)  END

IF @FLG = 1 
RETURN '-'+REVERSE(@HOLDER)+CASE WHEN CAST(@PART2 AS NUMERIC(19,4)) = 0 THEN '' ELSE @PART2 END 

RETURN REVERSE(@HOLDER)+CASE WHEN CAST(@PART2 AS NUMERIC(19,4)) = 0 THEN '' ELSE @PART2 END

END 

quote:

---

Nice function. It has some problems with negative numbers, I don't know if that is a requirement, if so it might need some extra code to handle the -

select dbo.formatnumber (-412)

-,412

select dbo.formatnumber (-126412)

-,126,412

I imagine negatives would need to be converted to a positive number then translated with the commas and then have the - stuck on the front again. Or something.

-------
Moo.

---

declare @n decimal(20,8), @c varchar(20)
select @n = -417543678.2425

select
    @c = coalesce(@c,'') + m
from (
    select top 100 percent
        n,
        case
            when n%10 = 0 then substring(convert(varchar,@n),n/10,1)
            when (n/10) < charindex('.',convert(varchar,@n))-1 and
                ((n/10) - charindex('.',convert(varchar,@n))+1)%3=0 and
                 n%5 = 0 and
                 substring(convert(varchar,@n),(n/10),1) <> '-' then ','
            else null
        end as m
    from
        dbo.tally
    where
        n/10<=len(convert(varchar,@n)) and
        n >= 10
    order by
        n) n
where
    m is not null
order by
    n

select @c

quote:

---

I should note that in this case I completely agree with Jeff and others that formatting is the job of the presentation layer ... I just saw the function and had to write a set-based version

Jay White
{0}

---

declare @t datetime
set @t = getdate()
--my version of the function
select dbo.formatnumber2(rand(convert(binary(4),newid()))* t1.n * 10000) from tally t1
select datediff(ms,@t,getdate())
set @t = getdate()
select dbo.formatnumber(rand(convert(binary(4),newid()))* t1.n * 10000) from tally t1
select datediff(ms,@t,getdate())

quote:

---

nothing is wrong with the UDF at all ... it technically works fine, it is just that choosing to solve this particular problem (or any formatting problem for that matter) within SQL server is NOT a good way to do it ... do it at the presentation layer.

- Jeff

---