Oracle 개행문자 찾기 - oracle gaehaengmunja chajgi

데이터의 값이 엔터가 들어간 문자를 찾기 위해서 테이블을 생성하고, 엔터가 들어가지 않은 데이터와 엔터가 들어간 데이터를 삽입하여 조회 후 엔터를 제거하는 쿼리를 나열 하겠습니다.

1. 테이블 생성

2. 데이터 생성

• USER_ID를 동일한 값을 지정하는 이유는 마지막에 치환할 때 홍길동은 아무런 변화가 없어야 하므로, 유효성 체크를 위해서 입니다. 만약, 아무런 문제가 없는 홍길동을 치환 시켜버린다면 더 큰 문제가 발생 할 수 있으니 테스트는 엔터 없는 데이터와 엔터가 있는 데이터를 치환할 때 문제가 없는지 파악합니다.

3. 데이터 확인

4. 엔터(ENTER) 데이터 찾기

• 데이터 중에서 ENTER가 들어간 문자를 'PWDENTER##!!' 임의의 값으로 변경하고 조회하는 방법 입니다.

5. 엔터(ENTER) 데이터 치환하기

5-1. 치환된 결과 확인

위 내용처럼 오라클 함수를 이용해서 엔터(ENTER)값을 치환할 수 있습니다.

사용되었던 SQL은 아래와 같습니다.

데이터를 텍스트 상자(Text Area) 등으로 입력한 경우 개행 문자(줄 바꿈)가 들어가는 경우가 있습니다. 이렇게 입력된 데이터 중 첫 번째 줄만 필요한 상황이 발생할 수 있습니다.

데이터 예시)

insert into test_table values ('첫번째 줄');
insert into test_table values ('첫번째 줄'||CHR(10)||'두번째 줄');
insert into test_table values (CHR(10)||'첫번째 줄');
insert into test_table values (CHR(10)||CHR(10)||'첫번째 줄');
insert into test_table values ('첫번째 줄'||CHR(10)||'두번째 줄'||CHR(10)||'세번째 줄');

* CHR(10)는 개행문자(줄바꿈)를 의미합니다.

위의 코드 처럼 다양한 케이스가 존재합니다.

첫 번째 줄만 존재하는 경우, 첫 번째 줄이 개행 문자가 들어간 경우, 개행 문자가 여러 개 들어가 있는 경우 등이 존재합니다.

우선 기본적인 방법으로 SUBSTR으로 개행문자(CHR(10))이 나올 때까지 문자를 자르면 됩니다. 여기서 문제는 CHR(10)이 몇번째 인덱스에 존재하는지 알아야 됩니다.

STEP1

먼저 CHR(10)의 인덱스를 구하는 쿼리입니다.

SELECT INSTR(message, CHR(10)) AS FIRST_LINE
  FROM test_table;

INSTR는 해당 문자열의 특정 문자의 인덱스를 찾을 때 사용하는 함수입니다.

* INSTR(문자열, 검색할 문자, 시작지점, n번째 검색단어)

STEP2

INSTR을 사용하여 인덱스를 구했다면 SUBSTR을 사용하여 문자를 자르면 됩니다.

SELECT SUBSTR(
                message
                , 0
                , INSTR(message, CHR(10))
            ) AS FIRST_LINE
  FROM test_table;

-실행 결과

실행 결과를 보면 단순히 첫번째 개행 문자를 사용한 경우 예외상황이 발생하였습니다.

 1. 첫 줄이 개행문자인 경우

 2. 개행문자가 없는 경우

STEP3

방금 전 발생한 예외상황 중 '1. 첫 줄이 개행 문자인 경우'에 있는 문제를 해결하기 위해 LTRIM을 사용하여 좌측 공백을 전부 제거하도록 하겠습니다.

* LTRIM(문자열, 옵션)

공백을 제거한 문자열의 개행문자 인덱스를 다시 구하여 SUBSTR에 적용을 시켜줍니다.

SELECT SUBSTR(
                message
                , 0
                , INSTR(LTRIM(message, CHR(10)), CHR(10))
            ) AS FIRST_LINE 
  FROM test_table;

-실행 결과

실행 결과 맨 앞줄의 공백은 제거되었습니다.

그런데 null값이 나오는 것을 확인할 수 있습니다. 그 이유는 개행 문자가 존재하지 않은 데이터가 존재하여 null값이 출력되는 것입니다.

STEP4

마지막으로 STEP2에서 발생한 예외사항 중 '2. 개행 문자가 없는 경우'를 해결하기 위해 인덱스가 0인 경우를 처리하겠습니다.

SELECT SUBSTR(
                message
                , 0
                , CASE WHEN INSTR(LTRIM(message, CHR(10)), CHR(10)) = 0 THEN LENGTH(message)
                       ELSE INSTR(LTRIM(message, CHR(10)), CHR(10))
                   END
            ) AS FIRST_LINE 
  FROM test_table;

-실행 결과

CASE문을 이용하여 인덱스가 0인 경우는 문자열 길이만큼을 지정해 주면 해결됩니다.