오라클 콤마 추가하기 (콤마 넣는 방법)

오라클 조회 결과에서 천 단위로 콤마를 추가하는 방법에 대해 알아봅시다. 첫 번째 방법은 REVERSE 함수와 REGEXP_REPLACE 함수를 사용하여 콤마를 추가하는 방법입니다. 두 번째 방법은 TO_CHAR 함수와 포맷 모델을 이용하는 것으로 콤마를 넣는 방법입니다.

REGEXP_REPLACE 함수로 콤마 추가하기

DUAL테이블을 통해 간단히 조회해 볼 수 있는 테스트 쿼리는 다음과 같습니다. 컬럼을 이렇게 지정하여 콤마를 추가할 수 있습니다.

WITH VW AS
(SELECT 123456789 AS NUM FROM DUAL)
SELECT REGEXP_REPLACE(REVERSE(REGEXP_REPLACE(REVERSE(TO_CHAR(NUM)), '([0-9]{3})', '\1,')), '^,')
FROM VW

콤마 추가 해석하기

숫자 123456789에 콤마를 추가하여 123,456,789로 변경하는 과정을 살펴봅니다. 컬럼 이름은 'NUM'으로 합니다.

1. NUM을 캐릭터로 변환하여 REVERSE 함수를 통해 역순으로 조회합니다.

=> REVERSE(TO_CHAR(NUM))

=> 123456789에서 987654321 으로 값이 변화합니다.

2. 정규표현식 리플레이스 함수로 3개의 숫자마다 그룹을 지정하여 콤마를 추가합니다.

=> REGEXP_REPLACE(REVERSE(TO_CHAR(NUM)), '([0-9]{3})', '\1,')

=> '([0-9]{3})' 에서 [0-9]가 숫자를 캡처하는 표현식이고 {3}은 정확히 3개의 숫자를 의미합니다. 소괄호를 통해 그룹을 진행합니다. 그리고 각 그룹 이후 콤마를 찍도록 '\1,' 값으로 대체합니다. \1은 캡처된 각 표현의 첫 번째 그룹입니다.

=> 987,654,321, 값을 반환합니다.

3. 콤마를 찍었으니 이제 반환한 값을 다시 REVERSE 합니다.

=> REVERSE(REGEXP_REPLACE(REVERSE(TO_CHAR(NUM)), '([0-9]{3})', '\1,'))

=> ,123,456,789 값을 반환합니다.

4. 해당 식은 3자리마다 콤마를 찍기 때문에 리버스를 사용했을 때, 3의 배수의 자리수면 제일 앞에 콤마가 있을 것이고 반대의 경우에는 제일 앞에 콤마가 없을 것입니다. 그리하여 제일 앞의 콤마를 제거하도록 합니다.

=> REGEXP_REPLACE(REVERSE(REGEXP_REPLACE(REVERSE(TO_CHAR(NUM)), '([0-9]{3})', '\1,')), '^,')

=> '^,' 에서 콤마로 문자열이 시작하는 경우 NULL로 리플레이스 합니다. REGEXP_REPLACE에서 3번 째 인수를 생략하면 NULL로 치환하라는 뜻입니다.

=> 123,456,789 값을 반환합니다.

WITH VW AS
(SELECT 123456789 AS NUM FROM DUAL
UNION ALL
SELECT 12345678 AS NUM FROM DUAL
UNION ALL
SELECT 1234567 AS NUM FROM DUAL
UNION ALL
SELECT 123456 AS NUM FROM DUAL
UNION ALL
SELECT 12345 AS NUM FROM DUAL
UNION ALL
SELECT 1234 AS NUM FROM DUAL
UNION ALL
SELECT 123 AS NUM FROM DUAL
UNION ALL
SELECT 12 AS NUM FROM DUAL
UNION ALL
SELECT 1 AS NUM FROM DUAL)
SELECT NUM,    
    REVERSE(TO_CHAR(NUM)),    
    REGEXP_REPLACE(REVERSE(TO_CHAR(NUM)), '([0-9]{3})', '\1,'),
    REVERSE(REGEXP_REPLACE(REVERSE(TO_CHAR(NUM)), '([0-9]{3})', '\1,')),    
    REGEXP_REPLACE(REVERSE(REGEXP_REPLACE(REVERSE(TO_CHAR(NUM)), '([0-9]{3})', '\1,')), '^,'),
    --숫자 그대로 리버스한 경우
--    REVERSE(NUM) AS REVERSE_AS_NUMBER,
    --그룹 지정 문제
--    REGEXP_REPLACE(REVERSE(TO_CHAR(NUM)), '[0-9]{3}', '\1,') AS ERR_REGEXP_REPLACE_1,
    --캡처되지 않은 그룹 지정
--    REGEXP_REPLACE(REVERSE(TO_CHAR(NUM)), '([0-9]{3})', '\2,') AS ERR_REGEXP_REPLACE_2,
    --그룹 지정, 캡처 지정 문제
--    REGEXP_REPLACE(REVERSE(TO_CHAR(NUM)), '[0-9]{3}', '\1,\2,') AS ERR_REGEXP_REPLACE_3,
    REGEXP_REPLACE(REVERSE(TO_CHAR(NUM)), '([0-9]{2})([0-9]{1})', '(그룹1:\1)/(그룹2:\2)/(그룹3:\3)') AS EXAMPLE,
    REGEXP_REPLACE(REVERSE(TO_CHAR(NUM)), '(([0-9]{2})([0-9]{1}))', '(그룹1:\1)/(그룹2:\2)/(그룹3:\3)') AS EXAMPLE,
    '1'    
 FROM VW
;

TO_CHAR 함수로 콤마 넣기

캐릭터 타입 형변환의 포맷 모델을 통해서 콤마를 넣는 방법입니다.

장점은 쿼리의 직관성과 사용하기 쉽다는 것이고, 단점은 포맷과 일치하지 않는 경우 '# # #' 과 같이 숫자가 정상적으로 표시가 되지 않는다는 것입니다. 숫자의 자리수가 고정적으로 정해지는 자료형태라면 사용하기 괜찮은 방법이라 할 수 있습니다.

FM 포맷은 앞뒤공백을 생략한다는 뜻입니다. (no leading or trailing blanks)

예시자료는 포맷을 999 이런 식으로 하였는데, '9'는 해당 자리에 0 또는 값이 없는 경우 블랭크로 지정합니다.

'0'을 포맷으로 지정하는 경우에는 '9'와 다르게 블랭크가 아니라 '0'으로 값이 채워져서 길이가 고정됩니다.