Oracle SQL: How do I convert the substrings in a string using to_char

Question

The to_char works fine when applied to single match like below.

select to_char('54523234', '99,999,999,999')

I use regex to find the match of only the currencies in the string. But when I apply to_char to the first group, it doesn't work

SELECT regexp_replace ('24444.88, 54523234.78, and 1044.52 are numbers in this example.', 
'(([0-9]+){1,10})+\.([0-9]{2})', to_char('\1'.'99,999,999,999'))
FROM dual;

I want the output string to be displayed like: 24,444.88, 54,523,234.78 and 1,044.52 are numbers in this example.

Answer 1

Here you go brother, that was a fun one:

SELECT regexp_replace(regexp_replace ('234234234.88, 24444.88, 54523234.78, and 1044.52 are numbers in this example.', 
'(([0-9]{3})\.)',
',\1'),'(([0-9]{3}),)',',\1')
FROM dual

Answer 2

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE OR REPLACE FUNCTION reverseString(
  str VARCHAR2
) RETURN VARCHAR2
AS
  v_str VARCHAR2(4000);
BEGIN
  FOR i IN REVERSE 1 .. LENGTH( str ) LOOP
    v_str := v_str || SUBSTR( str, i, 1 );
  END LOOP;
  RETURN v_str;
END;
/

CREATE OR REPLACE FUNCTION insertDecimalSeparators(
  str VARCHAR2
) RETURN VARCHAR2
AS
  v_str VARCHAR2(4000) := str;
  v_pos NUMBER(4,0);
  v_num VARCHAR2(4000);
  v_rpl VARCHAR2(4000);
  c_re  CONSTANT CHAR(9) := '\d+\.?\d*';
BEGIN
  FOR i IN REVERSE 1 .. REGEXP_COUNT( str, c_re ) LOOP
    v_pos := REGEXP_INSTR( str, c_re, 1, i );
    v_num := REGEXP_SUBSTR( str, c_re, 1, i );
    v_rpl := REGEXP_REPLACE(
               reverseString( v_num ),
               '((\d*\.)?\d{3})',
               '\1,',
               1,
               0
             );
    IF SUBSTR( v_rpl, -1 ) = ',' THEN
      v_rpl := SUBSTR( v_rpl, 1, LENGTH( v_rpl ) - 1 );
    END IF;
    v_str := SUBSTR( v_str, 1, v_pos - 1 ) || reverseString( v_rpl ) || SUBSTR( v_str, v_pos + LENGTH( v_num ) );
  END LOOP;
  RETURN v_str;
END;
/

Query 1:

WITH data ( str ) AS (
            SELECT '244445.88, 54523234.78, and 1044.12345678 are numbers.' FROM DUAL
  UNION ALL SELECT 'abc123456' FROM DUAL
  UNION ALL SELECT '123, 12, 1234, 123.1' FROM DUAL
  UNION ALL SELECT '123.0123456789' FROM DUAL
)
SELECT str,
       insertDecimalSeparators( str ) AS commaSep
FROM   data

Results:

|                                                    STR |                                                   COMMASEP |
|--------------------------------------------------------|------------------------------------------------------------|
| 244445.88, 54523234.78, and 1044.12345678 are numbers. | 244,445.88, 54,523,234.78, and 1,044.12345678 are numbers. |
|                                              abc123456 |                                                 abc123,456 |
|                                   123, 12, 1234, 123.1 |                                      123, 12, 1,234, 123.1 |
|                                         123.0123456789 |                                             123.0123456789 |