Page 1 of 1
Function to separate every three digits with a comma
Posted: Thu Nov 22, 2012 10:35 am
by rajasekhart
Hi,
I need to write a User Defined Function to separate every three digits of a number with a comma from the right hand side..
Eg: i will pass a number 1234567890 as a parameter to the function.
and i need the result as 1,234,567,890 .
Please help me..
Thanks ,
Raj!!
Re: Function to separate every three digits with a comma
Posted: Fri Nov 23, 2012 3:25 pm
by JimKnicely
Raj,
Can't you simply use the TO_CHAR function? Something like this:
Code: Select all
dbadmin=> SELECT TO_CHAR(1234567890, '999,999,999,999,999,999,999,999');
TO_CHAR
----------------------------------
1,234,567,890
(1 row)
Re: Function to separate every three digits with a comma
Posted: Fri Nov 23, 2012 4:56 pm
by id10t
Hi!
IMHO - from performance and safety view it's a best solution(I mean Jim's solution).
It can be done with "REGEXP look behind" or UDF(simple loop over number as string from end), but if REGEXP - performance will impact, if UDF - performance and safety.
Re: Function to separate every three digits with a comma
Posted: Sun Nov 25, 2012 2:43 pm
by id10t
UPDATE
RegExp solution:(ugly solution)
REGEXP itself: (?<=\d)(?=(\d{3})+(?!\d))
Example:
Code: Select all
dbadmin=> select regexp_replace(54638297869873276253625346125::varchar,'(?<=\d)(?=(\d{3})+(?!\d))',',');
regexp_replace
----------------------------------------
54,638,297,869,873,276,253,625,346,125
(1 row)