서론
이번에도 역시 문자열을 가공하는 함수들에 대해서 알아보고자 한다. 나름 자주 쓰이는 함수이니 익숙해지면 좋을 것 같다.
본론
LPAD, RPAD
--LPAD('문자열' 또는 컬럼명, 바이트 [,채울 문자]))
--이거 자리수가 아니라 바이트 수 같은데? 숫자 영문 1, 한글, 특수문자 2바이트씩
--바이트 수 만큼 다 찰 때까지 해당 문자열을 가장 왼쪽에 반복해서 출력
--문자열을 붙였을때의 크기가 바이트 수를 넘어서면 붙지 않는다.
--채울 문자 생략 시에는 공백을 문자대신 끼워서 반환한다.
--RPAD는 오른쪽에 붙임
--채울문자를 넣어야 하는데 자리수가 부족하면 안 채워짐
--양쪽에 삽입하기 위해서는 각각 사용할 수 밖에 없다
SELECT RPAD(LPAD('1234', 6, '*'), 8, '*') AS RETURN FROM DUAL;
select lpad('123456789', 10, '*') as return from dual;
select lpad('123456789', 11, '*') as return from dual;
select rpad('123456789', 10, '가') as return from dual;
--가는 2바이트를 차지함 따라서 10바이트가 넘기때문에 가가 붙지 않는다
select rpad('123456789', 11, '나') as return from dual;
--나도 2바이트를 차지하지만 11바이트 이기 때문에 9+2바이트 나가 붙을 수 있다.
LPAD는 문자열 또는 컬럼의 크기가 바이트 수보다 작을때 해당 채울 문자 만큼 반복해서 가장 왼쪽부터 꽉 채워주는 함수이다. 바이트 수보다 더 커 버리면 문자가 채워지지 않는다. 이게 무슨 말이냐면 아무 일도 일어나지 않는다고 보면 된다. RPAD는 반대인 오른쪽으로 채워준다. 채울 문자를 생략하면 공백이 들어간다.
LTRIM, RTRIM, TRIM
--LTRIM('문자열' 또는 컬럼명 [, '제거할 문자']
--제거할 문자를 왼쪽부터 찾다가 제거 후 다음에 다른 문자가 오면 스탑
--제거할 문자를 왼쪽부터 찾아 지우다가 또 같은문자가 연달아 있으면 또 지움
--RTRIM은 오른쪽 부터 찾음
--앞에 공백이나 다른문자가 있으면 못 지움
select ltrim('abaabbbcdea', 'a') from dual;
select ltrim('baaaabbbcdea', 'a') from dual;
select ltrim(' aaaabbbcdea') from dual;
select ltrim('가나다라', '가'), rtrim('가나다라', '나') from dual;
--TRIM('문자열' 또는 컬럼명)
--앞뒤 공백을 모두 지운다 중간 공백은 못 지움
select trim(' dadf ') from dual;
링크박스
특정 문자를 가장 끝에서 부터 제거 할때 사용한다. 예를들어 성이 김씨인 사람의 성만 지우고 싶다면 SELECT LTRIM('COL1', '김') FROM TABLE1;
RTRIM은 오른쪽 끝에 있는 것을 지울 때 사용한다. LTRIM, RTRIM은 각각 왼쪽, 오른쪽 끝에서 제거할 문자를 찾는데 가장 첫번째에 없으면 아무일도 일어나지 않는다. 한마디로 가장 끝에 있는 문자열을 지울 때 사용되며 중간에 있는 것들은 지울 수가 없다. LTRIM(컬럼, '김')이라고 하고 김을 지울 건데 이름이 김김무 라면 김김 둘다 지워져 원하는 결과를 얻을 수 없다. 이름이 LTRIM을 이용하여 똑같이 김을 제거 할건데 김김무김 이라면 김김 까지만 지워지고 다음에 또 보이는 김은 제거되지 않는다. 제거할 문자를 넣지 않으면 공백을 제거해준다.
TRIM은 앞뒤에 있는 공백만을 제거해준다. 중간 공백은 제거하지 못한다.
REPALCE, TRANSLATE
--REPLACE('문자열'또는 컬럼명, '문자1', '문자2')
--문자열에서 특정 단어를 다른 단어로 변환
SELECT REPLACE('123412', '12', '**') FROM DUAL;
SELECT REPLACE('123421', '12', '**') FROM DUAL;
--STUDENT 테이블을 이용하여 jumin 컬럼을 생년월일만 공개되고 뒷자리는 *로 표시되도록 하여라
select replace(jumin, substr(jumin, 7), '*******') from student;
--STUDENT 테이블을 이용하여 tel 컬럼을 전화번호 앞자리만 #로 표시되도록 하여라
select replace(instr(tel, ')')+1, instr(tel, '-') -1, '#') from student;
--TRANSLATE('문자열'또는 컬럼명, '문자1', '문자2')
--문자열에서 문자1을 찾아서 문자2로 문자 대 문자로 매칭시켜 변환한다
--문자1의 개수와 문자2의 개수가 같으면 정확히 순서대로 일대일 매칭되어 모든 패턴이 변환된다
--문자1의 개수 < 문자2의 개수 이면 문자2에 없는 패턴은 치환되지 않는다
--문자1의 개수 > 문자2의 개수 이면 문자2에 없는 문자1의 패턴은 삭제된다
--각 자리수에 있는 것대로 1:1로 치환 된다
SELECT TRANSLATE('123412', '12', '**') FROM DUAL;
--1은 *로 2도 *로 (1:1매칭)
SELECT TRANSLATE('12421', '31', '***') FROM DUAL;
--3은 *로 1도 *로 변경하는데 *에 매칭되는 값이 문자1 쪽에 없으므로 끝
SELECT TRANSLATE('12421', '312', '**') FROM DUAL;
--3은 * 1도 *, 2는? 문자2쪽에 없으므로 2는 삭제 해버린다.
REPLACE 함수는 문자열을 찾아 다른 문자열로 바꿔주는 함수이다. 뭐 특별한건 없다. 하지만 TRANSLATE 함수가 좀 헛갈리는데, TRANSLATE 역시 문자열을 치환해주는 함수이긴 하지만 다른 점이 있다. REPLACE는 통으로 바꿔준다고 볼 수 있지만 TRANSLATE는 각각 1:1 매칭을 해서 변경하게 해준다. 보통 문자1과 문자2 자리수를 맞춰 1:1로 변경하게끔 처리하지만 위의 예제처럼 숫자가 다를 수도 있다. 문자1이 2자리, 문자2가 3자리라면 문자2의 3번째 문자는 매칭되는 것이 없어 아무런 일도 일어나지 않는다. 하지만 문자1이 3자리 문자2가 2자리일 경우에는 문자1의 3번째 자리가 매칭 할 것이 없는데 다른 점이라면 삭제가 되어버린다. 이 두가지를 계속해서 이것저것 해보면서 익히면 좋다.
--professor 교수아이디에서 특수문자 제거 select replace(id, '-', ''), translate(id, '1!#$%!$%-', '1') --1은 1로 치환 나머지는 삭제 from professor;
위와 같은 TRANSLATE 함수는 1은 1로 매칭되니 변화가 없겠지만 나머지 특수문자들은 매칭되는 것들이 없어 삭제가 된다. 따라서 특정 특수문자들만 삭제하고 싶다면 위와 같이 해주면 된다. 전화번호도 예를들어 앞자리를 ###으로 변경하고 싶을때 SUBSTR를 이용, 앞 자리부분만 잘라 REPLACE로 변경 할 수도 있지만 앞자리가 4자리이거나 3자리 일수도 있고 (REPLACE에서 ###으로 변경되게 했지만 4자리라면 ####이 와야 된다.) 뒷자리에 똑같은 숫자가 있다면 뒷자리 까지 REPLACE 될 것이다. 따라서 이럴 경우에는 TRANLATE(전화번호, '1234567890', '##########')로 특정 자리만 SUBSTR로 빼서 변경한다면 문제 없이 변경이 가능 할 것 같다. 이 방법이 현업에서 쓰는지는 모르겠다. 순전히 필자의 생각이다.
실습문제
--1) student 테이블에서 1전공(deptno1)이 101번인 학생들의 이름을 출력하되, --가운데 글자만 '*'로 표시되게 출력하여라 --select * from student; select replace(name, substr(name, 2, 1), '*') from student where deptno1 = 101; --2) student 테이블에서 전화번호 국번만 '#' 처리하여 출력하여라 --ex 051) ###-1700 select substr(tel, 1, instr(tel, ')')) || translate(substr(tel, instr(tel, ')')+1, (instr(tel, '-')) - (instr(tel, ')')+1)), '0123456789', '##########') || substr(tel, (instr(tel, '-'))), tel from student; --3) professor 테이블을 이용하여 id와 email 주소의 id(email 컬럼의 @ 앞 문자)가 --다른경우 email 주소를 id로 변경하여 출력하여라. --ex)송도권 id : powerman, email id : pman, 새로운 email: powerman@power.com --select * from professor; select '송도권 id : ' || id || ', email id : ' || substr(email, 1, instr(email, '@') -1) || ', 새로운 email: ' || id || substr(email, instr(email, '@')) from professor where id <> substr(email, 1, instr(email, '@') -1);
결론
각 함수들을 많이 연습하여 어떠한 상황일때 필요한 함수가 기억날 정도로 해야 될 것이다. 생각보다 설명을 어렵게 썼나.. 하는 생각도 든다. 실습문제에 대한 샘플 데이터는 문법 공부 4번에 파일로 있으니 가져다가 돌리자.
