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) -- HOLDS 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:

---

ALWAYS return raw data to the clients and let them do the formatting.

---

quote:

---

the guy could be doing this to please his girlfriend for all we know

---