신기한 연구소

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

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번째 문자가 없기에 필요 없는 값!

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

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

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

SQL 관련 포스팅 중인데요.

정말 다양한 기능이 존재한다는 것을 새삼 느끼는 요즘입니다.

실전에서 자주 사용하기도 하지만

한번 더 기초부터 튼튼히 다시 공부하니 새롭고 좋네요.

이번에 포스팅할 주제는 바로 WITH절과 MERGE 절입니다.

둘 다 자주 편리하게 자주 사용하는데

한번 더 자세히 살펴보겠습니다.

WITH문

다양한 서브 쿼리를 한 곳에 정리(정의)해서 사용한다면 가독성도 좋고

편리하게 사용할 수 있지요?

게다가 그 안의 서브 쿼리를 재 사용도 가능하다면 1석 2조가 아닐까 싶습니다.

바로 WITH문이 그런 기능을 제공합니다.

위 쿼리를 보면 WITH 문 뒤에 별칭 DP, EP, CN을 지정하고

그 뒤로 원하는 서브쿼리를 생성했습니다.

별칭은 또 다른 임시 테이블명처럼 사용할 수 있습니다.

그리고 CN을 보면 FROM절에 DP라는 WITH문의 별칭을 재사용하고 있습니다.

WITH문에서 원하는 서브쿼리를 작성한 뒤 별칭을 지정합니다.

그리고 SELECT 문을 사용하는데

테이블 명 또는 WITH문의 별칭을 테이블처럼 사용해서 조회를 할 수 있습니다.

다양한 서브쿼리를 사용해서 데이터를 추출하고 FROM절에서 인라인 뷰를

많이 사용하다 보면 쿼리가 정말 복잡해지고 가독성도 떨어지게 됩니다.

이렇게 WITH문을 사용하면 별도로 관리가 되기에 쉽고 편하게 사용할 수 있답니다.

또한 서브쿼리들을 대사 하기도 편하겠지요? 별칭 기준으로 따로 조회해서 검증하면 되니깐요.

MERGE

화면을 통해 어떤 데이터를 받았습니다.

그런데 이 데이터가 기존에 있던 데이터인지 아니면 신규인지..

프로그램에서 먼저 SELECT문을 요청해서 해당 데이터에 대한 정보를 가져오면

1건의 처리를 위해 커넥션이 2번 일어나게 됩니다.

그런 경우 한 번에 처리할 수 있게 하는 구문이 바로 MERGE입니다.

구문 형식 먼저 살펴보겠습니다.

MERGE INTO 입력 또는 수정할 테이블

USING (원본 테이블, 소스 테이블)

ON (조건)

WHEN MATCHED THEN UPDATE (조건 데이터가 존재하면)

WHEN NOT MATCHED THEN INSERT (조건 데이터가 존재하지 않으면)

구문 형식만 보면 이해가 잘 안 됩니다.

이제 실제 쿼리를 확인해보겠습니다.

위 쿼리를 같이 보겠습니다.

MERGE INTO 다음에 EMP A로 입력 또는 수정할 대상 테이블을 지정했습니다.

USING 다음은 원본(소스) 테이블을 서브쿼리로 지정했습니다.

ON 다음은 대상 테이블과 원본 테이블에 대한 조건식으로 조인을 했습니다.

여기까지 보면 원본 테이블에서 CLUBNO가 NULL인 컬럼을 조회해서

대상 테이블 EMP A와 EMPNO로 조인해서 해당하는 결과를 추출합니다.

그리고 이제 수정을 할지 입력을 할지 선택하도록 합니다.

대상 테이블에 대상이 존재하면 UPDATE를 실행하도록

WHEN MATCHED THEN UPDATE SET 원하는 컬럼을 수정합니다.

WHEN NOT MATCHED INSERT VALUE 원하는 컬럼을 입력합니다.

위 구문을 보면 첫 번째 쿼리는 MATCHED하면 기존에 일치하는 데이터가 있기에 UPDATE를 해주고

두 번째 쿼리는 NOT MATCHED면 기존에 일치하는 데이터가 없기에 INSERT를 실행합니다.

오라클 10g 이후에는 위 쿼리처럼 사용할 수 있습니다.

이제는 WITH와 MERGE에 대해 잘 알게 되었으니

실전에서 잘 사용할 수 있으리라 믿습니다.

잘못된 내용이나 수정이 필요하면 댓글 주세요.

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