SQL 내장 함수에는 여러 가지 종류가 있다.
이번에는 그 중에서 문자 함수에 대해 다뤄보도록 한다.
1. 대 소문자를 바꿔주는 UPPER, LOWER, INITCAP 함수
UPPER(문자열) : 괄호 안 문자 데이터를 모두 대문자로 변환하여 반환
LOWER(문자열) : 괄호 안 문자 데이터를 모두 소문자로 변환하여 반환
INITCAP(문자열) : 괄호 안 문자 데이터 중 첫 글자는 대문자로, 나머지 문자를 소문자로 변환 후 반환
select ename, upper(ename), lower(ename), initcap(ename) from emp;
예시 테이블에서 문자함수를 이용하여 사원들의 이름을 각각 대문자, 소문자, 첫글자만 대문자로 바꿀 수 있다.
2. 문자열 일부를 추출하는 SUBSTR 함수
- SUBSTR( [문자열데이터], [시작위치], [추출길이] ) 로 구성
- 문자열데이터의 시작 위치부터 추출 길이만큼 추출한다.
- 시작위치가 음수일 경우에는 마지막 위치부터 거슬러 올라간 위치(뒤에서 부터 센다.)부터 시작한다.
- 추출 길이가 지정되지 않은 경우에는 끝까지 추출한다.
select ename, substr(ename,1,3), substr(ename,2), substr(ename,-2) from emp;
각각 1번째 위치부터 3번째 위치까지 추출
2번째 위치부터 끝까지 추출
-2번째 위치(뒤에서 두번째 위치) 부터 끝까지 추출
하는 경우이다.
3. 문자열 길이를 구하는 LENGTH 함수
- 선택한 열의 문자열 길이를 반환한다.
- LENGTH( [문자열] )
select job, length(job) from emp;
4. 특정 문자의 위치를 찾는 INSTR 함수
- INSTR( [대상 문자열 데이터], [찾는 문자], [시작할 위치], [찾는 문자가 몇번째 문자인지 지정] )
- 시작할 위치와 몇번째 문자인지를 지정하지 않으면 기본값은 둘 다 1이다.
select job, instr(job, 'l'),instr(job, 'L'),
instr(job, 'A', 3), instr(job, 'A', 1, 2)from emp;
사원들의 직업 에 L이 들어가는 위치와 A가 들어가는 위치를 찾는다.
select instr(job, 'l')먼저 각 필드의 값들은 대소문자를 구분하기 때문에 소문자 l을 찾을 수 없어서 모두 0이라고 나온다
select instr(job, 'L')때문에 위와 같이 대문자 L로 고친 경우에만 L이 2번째 3번째.. 에 있다고 나타난다.
select instr(job, 'A', 3)대문자 A를 찾는데 시작 위치가 3이다.
SALESMAN의 경우 2번째 위치에 A가 있지만 시작위치보다 앞에 있으므로 시작위치 다음으로 가장 앞쪽에 위치한
SALESM'A'N 을 반환하여 7번째 자리에 있다고 출력된다.
select instr(job, 'A', 1, 2)대문자 A를 찾는데 시작위치는 1이지만 2번째 위치한 A를 찾는 SQL문이다.
마찬가지로 SALESMAN에서 A가 두번째로 나오는 SALESM'A'N의 위치를 반환한다. 따라서 출력값은 7
MANAGER에서도 두번째 A인 MAN'A'GER 의 위치를 반환하므로 출력값은 4
5. 특정 문자를 다른 문자로 바꾸는 REPLACE 함수
- REPLACE( [문자열데이터], [찾는문자], [대체할 문자] )
- 대체할 문자를 지정하지않으면 공백으로 채워진다.
SALESMAN이라는 직업에 MAN이 들어가므로 이것을 WOMAN으로 바꿔보자.
select job, replace(job, 'MAN', 'WOMAN') from emp;
성공적으로 바뀌었지만 MANAGER라는 직업도 WOMANAGER로 바뀌어버렸다.
이것을 해결하기 위해서 위에서 배운 함수들을 사용해서 올바른 결과를 출력해보도록 하자.
먼저 SALESMAN 의 'MAN'은 바뀌고 MANAGER의 'MAN'은 바뀌지 않아야 한다. 둘의 차이는 MAN이 앞에 있냐 뒤에 있냐 로 볼 수 있다.
먼저 SUBSTR() 함수를 이용하여 각 직업의 뒤에서 3글자를 추출한다.
그리고 REPLACE() 함수를 이용해 이 추출한 3글자들에 대해 'MAN'이라는 글자를 찾아서 'WOMAN'으로 바꿔준다.
select job, substr(job, -3), replace(substr(job, -3),'MAN','WOMAN') from emp;
다시 REPLACE() 함수를 이용하여 각 직업에서 3글자를 추출한 후 위에서 REPLACE() 했던 결과로 바꿔준다.
select job, substr(job, -3), replace(substr(job, -3),'MAN','WOMAN'),
replace(job,substr(job,-3),(replace(substr(job, -3), 'MAN','WOMAN'))) from emp;
성공적으로 바뀐 것을 볼 수 있다.
위처럼 복잡하게 하지 않고
select replace(job,'SALESMAN','SALESWOMAN') from emp;로 간단하게 고치는 방법도 있지만 함수 안에 함수 사용을 연습해보기 위해 복잡하게 구성해보았다.
6. 데이터의 빈 공간을 특정 문자로 채우는 LPAD, RPAD 함수
- LPAD( [문자열 데이터], [데이터의 자릿수], [빈 공간에 채울 문자] )
- RPAD( [문자열 데이터], [데이터의 자릿수], [빈 공간에 채울 문자] )
- 빈 공간에 채울 문자를 선택하지 않으면 공백으로 채워진다.
select job, lpad(job, 10, '#'), rpad(job, 10, '#'), lpad(job, 10), rpad(job, 10) from emp;
LPAD에서는 왼쪽에 #과 공백, RPAD에서는 오른쪽에 #과 공백이 10자리수만큼 들어간 것을 확인할 수 있다.
7. 문자열 데이터를 합치는 CONCAT 함수
- CONCAT( [문자열1], [문자열2] )
- || 연산자로도 쉽게 가능하다.
select ename, job, concat(ename, job), concat(ename, concat(':', job)),
ename||job, ename||':'||job from emp;
이름과 직업을 연결해보았다. 공백없이 연결하게 되면 알아보기 힘드므로 콜론(:)을 중간에 넣었다.
CONCAT 함수는 3가지 문자열을 연결할 수는 없으므로 CONCAT(ename, CONCAT(':',job)) 처럼 이중으로 사용하면 3개 이상의 문자열도 연결할 수 있다.
다만 || 연산자를 이용하면 손쉽게 여러개의 문자열을 연결하는 것이 가능하다.
8. 특정 문자를 지우는 TRIM, LTRIM, RTRIM 함수
- TRIM( [삭제옵션] [삭제할 문자] from [원본 문자열 데이터] )
-> 삭제옵션 없음 : 양 끝의 문자 제거
-> 삭제옵션 LEADING : 왼쪽의 문자 제거
-> 삭제옵션 TRAILING : 오른쪽의 문자 제거
- LTRIM( [원본 문자열데이터], [삭제할 문자 집합] )
- RTRIM( [원본 문자열데이터], [삭제할 문자 집합] )
- 삭제할 문자를 지정하지 않으면 공백을 제거한다.
select trim('_' from '__hello world!__') as both,
trim(leading '_' from '__hello world!__') as left,
trim(trailing '_' from '__hello world!__') as right,
trim(both '_' from '__hello world!__') as both,
ltrim('__hello world!__', '__h') as ltrim,
rtrim('__hello world!__', 'd!__') as rtrim from dual;'__hello world!__'라는 문자열에서 trim()함수를 이용해 양쪽, 왼쪽, 오른쪽 삭제,
LTRIM, RTRIM 을 이용해 왼쪽, 오른쪽 삭제를 해보자.
TRIM 함수에서 옵션에 따라 양쪽이나 왼쪽 또는 오른쪽의 '_' 문자가 모두 사라지는 것을 볼 수 있다.
LTRIM 함수에서는 왼쪽의 문자열 '__h'가 삭제되었고
RTRIM 함수에서는 오른쪽의 문자열 'd!__'가 삭제된 것을 확인할 수 있다.