Function to separate every three digits with a comma

[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!!

[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)

[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.

[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)