오라클 콤마 처리 - olakeul komma cheoli

Question

정규표현식을 이용하여 콤마로 분리된 문자열을 여러 행으로 변환하는 예제이다.

목차 Show

1. 소수점 없을 때
2. 소수점 있을 때
3. 주의사항

SELECT
REGEXP_SUBSTR(STR, '[^,]+', 1, LEVEL) AS SUBSTR
FROM (
SELECT 'A,B,C,D,' AS STR
FROM DUAL
)
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(STR, '[^,]+'))+1

# 오라클 3자리 숫자 , (콤마) 찍기

- 어떠한 가격을 가지고오거나 숫자의 크기가 큰 칼럼을 표기할 때는 가독성을 높여주기 위해 ,를 찍어주는 것이 좋은데 그럴 때 사용하면 좋은 함수이다.

단,Ibatis나 MYBatis에서는 오라클 함수를 되도록 사용하면 좋지 않음으로 주의가 필요

SELECT
<!-- 원하는 컬럼 콤마 찍어 올 수 있다. -->
  REGEXP_REPLACE(REVERSE(REGEXP_REPLACE( REVERSE(TO_CHAR(NVL(SUM("원하는 컬럼"), 0))), '([0-9]{3})','\1,')), '^,','') TODAY_PROFIT
    FROM COIN_USE
  WHERE TO_CHAR(COIN_USE_DATE,'YYYYMMDD') = TO_CHAR(SYSDATE,'YYYYMMDD') AND COIN_USE_CANCEL IS NULL

숫자(금액) 세자리마다 콤마 처리 및 소수점 2자리까지 출력방법

TO_CHAR( NUM , 'fm999,999,999,999,999,990.00' );

설명 : 9는 없으면 생략되고, 0은 생략되지 않습니다.

- fm(또는 FM)은 좌우 공백 제거

※ 주의사항 : 해당 숫자(NUM)가 기입한 숫자의 개수를 초과할 경우에 (정수 18개, 소수 2개) ###으로 표시되어 에러처럼 보이는 현상이 있음

-----------------------------------------------------------------

참고사이트 : http://flymc.tistory.com/entry/%EC%98%A4%EB%9D%BC%ED%81%B4-Oracle-%EC%97%90%EC%84%9C-%EC%86%8C%EC%88%98%EC%A0%90%EC%B2%98%EB%A6%AC-%EC%96%B4%EB%96%BB%EA%B2%8C-%ED%95%98%EC%A7%80

간혹 업무를 처리하다보면 콤마(,)나 슬래시(/) 등으로 구분된 문자열을 행으로 분리해야하는 경우가 있습니다.

오라클 10g에서는 관련 함수(Regexp_Substr)를 제공하고 있지만 9i이하에서는 쿼리를 작성하여 처리해야 합니다.

문자열에 'A, B, C, D' 가 들어있는 경우,

오라클 9i에서 문자열 분리하는 방법

SELECT SUBSTR (COL,
INSTR (COL, ',', 1, LEVEL) + 1,
INSTR (COL, ',', 1, LEVEL + 1) - INSTR (COL, ',', 1, LEVEL)
- 1) AS COL
FROM (SELECT ',' || 'A,B,C,D' || ',' AS COL
FROM DUAL)
CONNECT BY LEVEL <= LENGTH (COL) - LENGTH (REPLACE (COL, ',')) - 1

;

오라클 10g에서 문자열 분리하는 방법

Select Regexp_Substr('A,B,C,D','[^,]+', 1, Level) COL From Dual
Connect By Regexp_Substr('A,B,C,D', '[^,]+', 1, Level) Is Not Null

;

결과값

COL

------

A

B

C

숫자 3자리마다 콤마찍는 쿼리

//'1억2십만3백' 콤마찍기

//1. TO_CHAR : 간단하지만 '999,999,999,999' 선언한 포맷의 자릿수만큼 앞에 공백생김
1. TO_CHAR('100200300','999,999,999,999') AS toChar 

//2. REGEXP_REPLACE() 함수사용 : 조금 복잡하지만 공백없이 잘 됨
2. REGXP_REPLACE(REVERSE(REGEXP_REPLACE(REVERSE(TO_CHAE('100200300')),'([0-9]{3}','\1,')),'^,','') AS REGEXPREPLACE

REGEXP_REPLACE() 함수원리

1단계 : REVERSE()함수를 통해 값을 역순으로 정렬(뒷자리부터 콤마를 찍기위함)

2단계 : 패턴과 일치하는 값 뒤에 "," 콤마를 삽입

3단계 : REVERSE()함수를 통해 값을 다시 역순으로 정렬

4단계 : 맨 앞의 문자가 콤마","일 경우 공백으로 치환

//REGEXP_REPLACE() 함수 원리

SELECT REVERSE(TO_CAR('100200300')) AS STEP1
	  ,REGEXP_REPLACE(REVERSE(TO_CHAR('100200300')),'([0-9]{3})','\1,') AS STEP2
      ,REVERSE(REGEXP_REPLACE(REVERSE(TO_CHAR('100200300')), '[0-9]{3})','\1')) AS STEP3
      ,REGEXP_REPLACE(REVERSE(REGEXP_REPLACE(REVERSE(TO_CHAR('100200300')),'([0-9]{3})','\1')),'^,','') AS STEP4
FROM DUAL;

출처 : m.blog.naver.com/PostView.nhn?blogId=mk1126sj&logNo=220985940775&proxyReferer=https:%2F%2Fwww.google.com%2F

내가 원하는 것

-1234 -> 1,234 => 1. 소수점 없을 때

-1234.5 -> 1,234.5 => 2. 소수점 있을 때