SQL [5] - 문자 함수들

1. Dual : 문자함수에 앞서 오라클에서 dual이라는 기능을 설명해드립니다.

         테이블명 자리에 dual을 넣고 컬럼명 부분에 원하는 임의의 값을 넣으면, 임의의 값의 1행 테이블을 만들수 있습           니다.

-> 간단한 연습할 때 크게 유용하니 알아두시길 바랍니다. : )

 

예시)

select '가나다' from dual;

select 12000 from dual;

 

 

 

2. initcap : 첫문자는 대문자, 나머지는 소문자로 변환해줍니다.

bEnJaMin이라는 값을 실제 영어 대소문자법에 따라 첫글자를 대문자로 만들어주고 싶을때, 

아래처럼 사용합니다. (문자 값은 무조건 작은 따옴표로 묶어주세요.)

select initcap('bEnJaMin') from dual;

3. upper : 모든 문자를 대문자로 변환해줍니다.

select upper('bEnJaMin') from dual;

 

 

4. lower : 모든 문자를 소문자로 변환해줍니다.

select lower('bEnJaMin') from dual;

 

 

5. length : 해당 값의 글자수를 세줍니다.

select length('bEnJaMin') from dual;

 

 

6. concat : 두개의 문자열을 연결해줍니다. ||와 같은 기능입니다.

예를 들면, Professor과 Benjamin 두 개의 문자를 연결하려면

select concat('Professor','Benjamin') from dual;

 

 

 

7. substr : 부분 문자열을 추출해줍니다. 

SUBSTR(컬럼 또는 문자열, 시작 인덱스, 원하는 문자의 갯수) 

professor의 prof만 추출하고자 할 때,

select substr('professor',1,4) from dual;

하지만, 만약에 benjamin@gmail.com 이라는 메일 주소 중 gmail.com만 추출하고 싶으면 뒤에서 9번째 부터 9개의 글자를 추출해야합니다. 

select substr(benjamin@gmail.com',-9,9) from dual;

 

 

8. instr : 컬럼 또는 문자열에서 찾고자 하는 문자의 위치의 자리를 추출해줍니다.

INSTR(컬럼 또는 문자열, '찾고자하는문자', 시작인덱스, 몇번째 문자를 추출할 것인가 또는 생략시 첫번째 문자)

--> 위의 benjamin@gmail.com라는 이메일 주소에서 '@'가 몇 번째 문자에 해당하는지 확인할 수 있게 해줍니다.

select instr('benjamin@gmail.com','@') from dual;

*** Quiz. 그럼 아래의 교수테이블에서 교수이름과 이메일주소의 '@'전 까지의 문자를 '메일 ID' 컬럼으로 추출해보고자 합니다.

ex) captain@abc.net -> captain  

- 사용되는 함수 instr, substr, 알리아스

select * from professor;

 

*** 정답: Instr로 @의 위치를 찾고, 메일의 처음부터 @의 바로 앞 글자까지, 즉 '@-1'번째 숫자를 추출해서 

substr(컬럼,시작,끝) 위치로 적용해줍니다. 

 

select name, substr(email,1,instr(email,'@')-1) "메일 ID"

from professor

;

 

 

9. lpad : 왼쪽에 문자를 추가해줍니다. lpad(컬럼 또는 문자열, 전체자리수, 채울문자)

select ename, lpad(ename, 10, '#')
from emp

;

 

 

 

 

10. rpad : 오른쪽에 문자를 추가해줍니다. rpad(컬럼 또는 문자열, 전체자리수, 채울문자)

select ename, rpad(ename, 10, '#')
from emp

;

 

 

11. trim : 양쪽의 공백을 제거해줍니다. 아래처럼 trim의 유무에 따른 결과 값 차이가 있습니다.

select '    안녕하세요.     ' from dual; 

select trim('    안녕하세요.     ') from dual;

trim X

trim O

 

 

12. replace : 기존 문자를 다른 문자로 치환해주는 함수입니다. 

replace(컬럼명 또는 문자열, '문자1', '문자2') : 문자열에서 문자1을 문자2로 치환해줍니다.

select replace('12345','12','ab') from dual;

 

 

13. translate : 기존 문자를 다른 문자로 치환해주는 함수입니다. 글자대 글자로 매칭해준다는 특징이 있습니다. 바꿀 문자와 바꿔질 문자의 수가 동일하게 변환됩니다. 아래 동일한 조건에서 replace와 translate만 차이를 주어 결과 값을 실행해보겠습니다. 

select replace('1234567','1234','abcdef') from dual;

replace

select translate('1234567','1234','abcdef') from dual;

translate

*** 차이점 : replace는 1234가 abcdef로 변환되지만, translate는 글자수에 맞춰서 1234가 abcd만큼만 변환됩니다.