[HOW]오라클SQL, REPLACE()와 TRANSLATE() 쉽게 이해하고 사용하는 방법, ORACLE, 변환함수

안녕하세요. 신기한 연구소입니다.

SQL 개발을 하면 변환 작업을 해야 하는 경우가 있습니다.

잘못 입력된 값이나 언어를 변경하고 싶은 경우에도 사용할 수 있는데요.

특히 개발 중 운영 데이터를 내려서 작업하는 경우가 있어요.

그런 경우에도 변경 작업으로 민감 정보를 숨길 수 있답니다.

 

 

문자열 변경에 사용할 수 있는 함수 2개에 대해 알아봅니다.

A->B로 바꾸는데 왜 함수가 2개나 필요할까요?

기능이 좀 다르기 때문입니다.

 

REPLACE(문자열, 변경대상 문자열, 변경할 문자열)

 

REPLACE는 변환 함수로 흔하게 사용합니다.

변경대상 문자열을 찾아서 변경할 문자열로 바꿔주는 기능을 합니다.

 

 

REPLACE를 사용한 쿼리입니다.

원하는 것은 남자를 상징하는 MAN을 TEAM으로 변경하고 싶은 겁니다.

아마 여자들의 반발이 있었나 봅니다.

먼저 EMP 테이블의 JOB 컬럼에서 변경할 대상을 찾습니다.

JOB컬럼에서 ‘MAN’이라는 문자열을 찾게 됩니다.

그리고 ‘MAN’과 일치하는 문자열이 나오면 ‘TEAM’으로 변경하도록 합니다.

그 결과를 확인해보면 SALESMAN이 SALESTEAM으로 변경되었습니다.

하지만 원치 않는 결과도 나왔습니다.

바로 6, 8, 9행입니다.

‘MANAGER’인데 남자의 의미와 관계없지만 ‘MAN’ 일치해서

‘TEAMAGER’이라는 신조어를 만들어버렸습니다. ㅎㅎㅎ

그래서 데이터를 잘 확인하고 REPLACE를 사용해야 합니다.

 

TRANSLATE(문자열, 변경대상 문자열, 변경할 문자열)

 

이런~

이건 REPLACE와 거의 같아 보입니다.

그럼 어떤 부분이 다를까요?

TRANSLATE는 주의해서 자세히 살펴봐야 합니다.

먼저 예제를 살펴봅니다.

 

 

위 예제를 보면 EMP 테이블의 JOB에서 ‘A’를 ‘@’로 변경합니다.

그럼 REPLACE와 뭣이 다른 걸까요? ㅎㅎ

REPLACE와 함께 사용한 다음 예제를 보겠습니다.

 

 

다른 결과가 나왔습니다.

REPLACE는 ‘A’를 ‘에이’로 깔끔하게 변경했습니다.

하지만 TRANSLATE는 ‘A’를 ‘에’로 변경했습니다.

둘의 차이점이 무엇일까요?

예를 하나 더 보겠습니다.

 

 

TRANSLATE를 이해할 수 있는 쿼리입니다.

정말 잘 만들었네요.

먼저 이해를 돕기 위해 설명하겠습니다.

‘A’를 ‘@’로  TRANSLATE/REPLACE 하면 문자열의 모든 ‘A’를 ‘@’로 변경합니다.

‘CBA’를 ‘123’으로 REPLACE 하면 ‘CBA’와 일치하는 문자열을 ‘123’으로 변경하고

TRANSLATE 하면 ‘CBA’에서 C는 1과 B는 2와 A는 3과 매칭 되어 변경합니다.

 

A를 보면 ‘ABC’를 ‘@@@’로 TRANSLATE 합니다.

결과는 ‘@@@ @@@ @@@ DDD’입니다.

즉 ‘ABC’와 ‘@@@’는 순서대로 1:1 매칭 한다는 의미입니다.

B를 보면 ‘CBA’를 ‘123’으로 TRANSLATE 하면

‘C’ = ‘1’, ‘B’ = ‘2’, ‘A’ = ‘3’으로 각각 순서대로 매칭 해서 변경한다는 의미입니다.

‘CBA’라는 문자열을 ‘123’으로 하나의 문자열로 보고 변경한다는 의미가 아닙니다.

C를 보면 ‘AAA’는 ‘123’으로 TRANSLATE 하는데 문제가 있습니다.

‘A’ = ‘1’, ‘A’ = ‘2’, ‘A’ = ‘3’이라는 ‘A’ 값이 3가지라는 이상한 조건이 됩니다.

결과를 보면 알겠지만 ‘A’ = ‘1’만 처리하고 나머지는 무시해버립니다.

D를 보면 ‘ABCD’ = ‘#@’로 TRANSLATE 합니다.

‘A’ = ‘#’, ‘B’ = ‘@’인데 ‘C’, ‘D’는 매칭 값이 없습니다.

그럼 NULL로 처리됩니다.

결과를 보면 중간에 ‘C’, ‘D’ 자리는 비어 있음을 확인할 수 있습니다.

E를 보면 ‘AB’를 ‘#@%^’로 TRANSLATE 했습니다.

‘A’ = ‘#’으로 ‘B’는 ‘@’로 변환합니다.

%^는 검색 대상이 없으니 쓰레기 데이터가 돼서 사용할 일이 없네요.

 

결론은 이렇습니다.

REPLACE는 ‘ ‘로 감싼 문자열 전체 그 자체로 똑같은 경우에 변경을 하고

TRANSLATE는 ‘ ‘로 감싼 문자열 하나하나 순서대로 변경할 문자열과  

하나씩 매칭 해서 변경한다고 보면 됩니다.

 

(변환 이해)

REPLACE(‘문자열’ , ‘AB’, ‘에이비’)

문자열에서 ‘AB’를 찾아서 ‘에이비’로 변환

TRANSLATE(‘문자열’, ‘AB’, ‘에이비’)

문자열에서 ‘A’를 찾아서 ‘에’로 변환 ‘B’를 찾아서 ‘이’로 변환.

‘비’는 찾을 대상의 3번째 문자가 없기에 필요 없는 값!

 

잘못된 정보나 수정이 필요하면 댓글 주세요

아래 하트(공감) 버튼을 눌러서 더 다양한 글을 쓸 수 있게 응원 부탁드립니다. 감사합니다.