Format double string with thousand separator

Asked Jan 14 '16 at 01:27

Active Jan 14 '16 at 01:27

Viewed 559 times

I need to convert a Double to String and format it with thousand separator and 2 decimal place. I have done the conversion of Double to String in a function. But stuck at formatting the String. Is there any clean way to format the String? I am still using DB2 9.5 so VARCHAR_FORMAT() might not be an option for me.

CREATE FUNCTION BELSIZE.DBL2STR_2DP (VAR0 DOUBLE)
    RETURNS VARCHAR(50)

F1: BEGIN ATOMIC

DECLARE var0_2dp DOUBLE;
DECLARE sVar VARCHAR(50);
DECLARE dec_part VARCHAR(50);

SET var0_2dp = ROUND(VAR0, 2);

SET sVar = CAST(CHAR(ABS(DECIMAL(var0_2dp, 31, 2))) AS VARCHAR(50));
SET sVar = STRIP(sVar, LEADING, '0');

IF VAR0 < 0 THEN
    SET sVar = CONCAT('-', sVar);
END IF;

RETURN sVar;    

END

asked Jan 14 '16 at 01:27

bittersour

http://stackoverflow.com/questions/4377352/how-do-i-format-a-number-with-commas-in-t-sql – Phiter Jan 14 '16 at 01:31
@PhiterFernandes thanks for the link. But I am not using MS SQL. – bittersour Jan 14 '16 at 01:36
http://stackoverflow.com/questions/29852504/convert-to-currency-or-money-format-in-db2 – Phiter Jan 14 '16 at 01:37
@PhiterFernandes thanks for the link. I prefer to do the formatting in the function itself instead of creating distinct type. – bittersour Jan 14 '16 at 01:44
1

What is the reason you're trying to do that in the database? Database is for storing data; formatting is best done at the client. – mustaccio Jan 14 '16 at 03:00
@mustaccio I need this function to convert Double to String then concat this String with other strings – bittersour Jan 14 '16 at 03:11

Format double string with thousand separator

0 Answers0