문자열 다루기 핵심 TRANSLATE

아래 결과를 이해하는 것이 목표

SELECT DATA
     , TRANSLATE (DATA, ' 1234567890', ' ') AS "숫자만제거"
     , TRANSLATE (DATA, '1234567890'||DATA, '1234567890') AS "숫자만남기기"
     , TRANSLATE (LOWER(DATA), ' abcdefghijklmnopqrstuvwxyz' , ' ' ) AS "문자만제거"
     , TRANSLATE (LOWER(DATA), 'abcdefghijklmnopqrstuvwxyz'||DATA , 'abcdefghijklmnopqrstuvwxyz' ) AS "문자만남기기"
FROM (SELECT EMPNO||ENAME||HIREDATE AS DATA  
        FROM EMP )

 

 

DATA	숫자만제거	숫자만남기기	문자만제거	문자만남기기
7698BLAKE81/05/01	BLAKE//	7698810501	769881/05/01	blake
7782CLARK81/06/09	CLARK//	7782810609	778281/06/09	clark
7566JONES81/04/02	JONES//	7566810402	756681/04/02	jones
7902FORD81/12/03	FORD//	7902811203	790281/12/03	ford
7369SMITH80/12/17	SMITH//	7369801217	736980/12/17	smith
7499ALLEN81/02/20	ALLEN//	7499810220	749981/02/20	allen
7521WARD81/02/22	WARD//	7521810222	752181/02/22	ward
7654MARTIN81/09/28	MARTIN//	7654810928	765481/09/28	martin
7844TURNER81/09/08	TURNER//	7844810908	784481/09/08	turner
7900JAMES81/12/03	JAMES//	7900811203	790081/12/03	james
7934MILLER82/01/23	MILLER//	7934820123	793482/01/23	miller
7876ADAMS87/05/23	ADAMS//	7876870523	787687/05/23	adams
7788SCOTT87/04/19	SCOTT//	7788870419	778887/04/19	scott
1111YODA81/11/17	YODA//	1111811117	111181/11/17	yoda
7839KING81/11/17	KING//	7839811117	783981/11/17	king

 

TRANSLATE 만 이해하면 문자열 다루는 함수는 거의 다 이해한 것이나 다름이 없다.

 

 

 

 

TRANSLATE( 데이터, 검색문자 , 치환문자)

 

문제

SELECT TRANSLATE ('abcde', '1234567890', ' ')  FROM DUAL

정답은?

'abcde' 그대로 출력된다. '1234567890' 로 검색해서 일치하는 것이 없기 때문이다.

 

문제

SELECT TRANSLATE ('12345', '1234567890', '12345') A
     , TRANSLATE ('12345', '1234567890', '67890') B
  FROM dual

A,B 결과는?

여기서 중요한 것은 A도 똑같지만 특히 B가 왜 '67890'이라는 결과가 나왔는지다

 

'1234567890' 에서 '67890' 이 있으니까 '67890' 나왔다고 생각했다면 틀렸다.

SELECT TRANSLATE ('12345', '1234567890', '12345') A
     , TRANSLATE ('12345', '1234567890', '67890') B
     , TRANSLATE ('12345', '1234567890', '0000067890') C
  FROM dual

 C가 어떤 결과가 나올 것 같은가?

답은 '00000' 이다. 

즉, 앞서 A 도 '12345' 가 '12345' 가 그대로 출력된게 아니라 치환되어 '12345'가 된것으로 값이 잘 맞아 떨어져 그대로 출력하게 된 것 처럼 보인 것이다.

즉, A, B 다 치환된 결과이다.

 

 

'1234567890'  검색문자가 어떻게 치환되는지 알아보자

문제

SELECT TRANSLATE ('54321', '1234567890', '12345') A
     , TRANSLATE ('54321', '1234567890', '67890') B
     , TRANSLATE ('54321', '1234567890', '0000067890') C
  FROM dual

단순히 데이터를 '12345'에서 '54321' 로 변경했다. 결과가 어떻게 나올까?

아마도 B에서 복잡하게 느껴지는 사람이 많을 것 같다. 따라서 B를 기준으로 설명하겠다.

여기서 핵심은 검색문자와 치환문자 변경 규칙은 위치, 즉 인덱스에 정확히 대응된다는 것을 염두하자

 

1회차 TRANSLATE ('54321', '1234567890', '67890')   ===>  '04321'

데이터 첫번째 문자 '5' 를 검색문자에 같은 '5'가 존재한다. 치환문자에 같은 위치 '0' 으로 치환된다.

2회차 TRANSLATE ('04321', '1234567890', '67890')   ===>  '09321'

데이터 두번째 문자 '4' 를 검색문자에 같은 '4'가 존재한다. 치환문자에 같은 위치 '9' 으로 치환된다.

똑같이 5회차까지 진행되서 '09876' 이 나온 것이다. 

SELECT DATA
     , TRANSLATE (DATA, ' 1234567890', ' ') AS "숫자만제거"
     , TRANSLATE (DATA, '1234567890'||DATA, '1234567890') AS "숫자만남기기"
     , TRANSLATE (LOWER(DATA), ' abcdefghijklmnopqrstuvwxyz' , ' ' ) AS "문자만제거"
     , TRANSLATE (LOWER(DATA), 'abcdefghijklmnopqrstuvwxyz'||DATA , 'abcdefghijklmnopqrstuvwxyz' ) AS "문자만남기기"
FROM (SELECT EMPNO||ENAME||HIREDATE AS DATA  
        FROM EMP )

 

DATA	숫자만제거	숫자만남기기	문자만제거	문자만남기기
7698BLAKE81/05/01	BLAKE//	7698810501	769881/05/01	blake
7782CLARK81/06/09	CLARK//	7782810609	778281/06/09	clark
7566JONES81/04/02	JONES//	7566810402	756681/04/02	jones
7902FORD81/12/03	FORD//	7902811203	790281/12/03	ford
7369SMITH80/12/17	SMITH//	7369801217	736980/12/17	smith
7499ALLEN81/02/20	ALLEN//	7499810220	749981/02/20	allen
7521WARD81/02/22	WARD//	7521810222	752181/02/22	ward
7654MARTIN81/09/28	MARTIN//	7654810928	765481/09/28	martin
7844TURNER81/09/08	TURNER//	7844810908	784481/09/08	turner
7900JAMES81/12/03	JAMES//	7900811203	790081/12/03	james
7934MILLER82/01/23	MILLER//	7934820123	793482/01/23	miller
7876ADAMS87/05/23	ADAMS//	7876870523	787687/05/23	adams
7788SCOTT87/04/19	SCOTT//	7788870419	778887/04/19	scott
1111YODA81/11/17	YODA//	1111811117	111181/11/17	yoda
7839KING81/11/17	KING//	7839811117	783981/11/17	king

 

 

"숫자만제거"의 결과가 저렇게 왜 저렇게 나왔을까?

TRANSLATE (DATA, ' 1234567890', ' ')  핵심은 공백 활용과 치환할 문자가 없음을 이해하는 것이다.

검색문자 맨 앞에 ' ' 공백이 존재한다. 치환문자는 ' ' 공백만 존재한다. 

즉, 공백이 존재하면 공백으로 치환한다. 이후 검색문자 '1234567890'은 치환할 문자가 없으므로 null처리(제거)가 된다.

검색문자 이외 철자나, 특수문자는 그대로 남아 BLAKE// .... KING// 이런 결과가 나온 것이다.

 

"숫자만남기기"의 결과를 살펴보자

(DATA, '1234567890'||DATA, '1234567890')   핵심은 데이터 활용(||) 하는 것이다

원리는 이전 치환할 문자가 없다는 것을 이용한다.

검색문자에서 '1234567890'||DATA 중 '1234567890'는 치환문자 '1234567890' 대응되어 먼저 처리된다. 

"7698BLAKE81/05/01" 를 예를 들자면 치환문자  '1234567890' 0까지 처리된 직후 결과는 "숫자만제거" 결과인 "BLAKE//" 상태일 것이다. 이후 치환할 문자가 없으니 null처리 되어 숫자만 제외하고 다 null처리된 것이다.

 

문자 처리도 숫자처리와 원리는 완벽히 같다. 다만 대소문자 구분을 한다는 것을 알리기 위해 LOWER함수만 추가 썼을 뿐 모든 면에서 동일하다.

 

 

만약 당장 이해하기 어렵다면, 위 표를 보고 공식처럼 대입해서 사용하면 된다.