데이터베이스에서 함수란?
: Java로 생각했을 때 메소드와 같은 존재이다.
함수는 매개변수로 전달된 값들을 읽어서 계산한 결과를 반환한다.
- 단일행 함수: 8개의 값을 읽어서 8개의 결과를 리턴한다.
(매 행마다 함수를 실행한 후 결과를 반환한다.)
- 그룹 함수: 8개의 값을 읽어서 1개의 결과를 리턴한다.
(하나의 그룹별로 함수를 실행한 후 결과를 반환한다.)
※ 단일행 함수와 그룹 함수는 같이 사용할 수 없다.
왜냐하면 결과 행의 개수가 다르기 때문이다.
- 단일행 함수 -
문자열과 관련된 함수
- 한글은 한 글자 당 3byte로 취급한다.
예) '새'(3byte), 'ㅅ'(3byte), 'ㅐ'(3byte)
- 영어와 숫자, 특수문자는 한 글자 당 1byte로 취급한다.
(1) LENGTH / LENGTHB
: 결과값은 숫자(NUMBER)로 반환한다.
① LENGTH(문자열): 전달된 해당 문자열의 글자 수를 반환한다.
② LENGTHB(문자열): 전달된 해당 문자열의 바이트 수를 반환한다.
- 영어와 숫자, 특수문자(1byte)
SELECT EMEIL, LENGTH(EMAIL), LENGTHB(EMAIL)
FROM EMPLOYEE;
EMAIL에 영어와 숫자, 특수문자(@)가 있는데,
이들은 한 글자 당 1byte로 취급한다.
결과적으로 보면 문자열의 글자 수와 바이트 수가 같다.
- 한글(3byte)
SELECT EMP_NAME, LENGTH(EMP_NAME), LENGTHB(EMP_NAME)
FROM EMPLOYEE;
한글은 한 글자 당 3byte로 취급한다.
이름이 3글자이면 LENGTH는 글자 수를 반환하므로 3이 반환되고, LENGTHB는 바이트 수를 반환하므로 3 * 3 = 9byte가 반환된다.
(2) INSTR
: INSTR(문자열, 특정 문자, 찾을 위치의 시작값, 순번) - 문자열로부터 특정 문자의 위치값을 반환한다.
- 찾을 위치의 시작값과 순번은 생략이 가능하다.
- 결과값은 숫자(NUMBER)로 반환한다.
- 찾을 위치의 시작값은 1이나 -1로 기술한다.
이때, 1은 앞에서부터 시작(생략 시 기본값), -1은 뒤에서부터 시작한다는 뜻이다.
- 앞에서부터 두 번째에 위치하는 B의 위치값
SELECT INSTR('AABAAABBBAA', 'B', 1, 2)
FROM DUAL; -- DUAL은 가상 테이블∴ 앞에서부터 두 번째에 위치하는 B의 위치값: 7
찾을 위치의 시작값이 1이면 앞에서부터 시작한다. 그리고 순번은 2이므로 두 번째로 있는 B의 위치를 알려달라는 말이다. 그럼 'AABAAABBBAA' 여기서 이 진한 색의 B의 위치값을 앞에서부터 세면 7이 나온다.
- 뒤에서부터 두 번째에 위치하는 B의 위치값
SELECT INSTR('AABAAABBBAA', 'B', -1, 2)
FROM DUAL;∴ 뒤에서부터 두 번째에 위치하는 B의 위치값: 8
찾을 위치의 시작값이 -1이면 뒤에서부터 시작한다. 그리고 뒤에서 두 번째에 위치한 B('AABAAABBBAA')의 위치값을 알려면 앞에서부터 세면 된다. 그럼 8이 나온다.
(3) SUBSTR
: SUBSTR(문자열, 처음 위치, 추출할 문자 개수) - 문자열로부터 특정 문자열을 추출한다.
- 결과값은 문자열(CHARACTER)로 반환된다.
- 추출할 문자 개수는 생략 가능하다.(생략 시 문자열 끝까지 추출된다.)
- 처음 위치는 음수로 제시할 수 있다.(뒤에서부터 문자를 추출한다.)
- 앞에서부터 문자열 추출
SELECT SUBSTR('HELLO ORACLE', 3, 4)
FROM DUAL;
앞에서 세 번째 글자부터 4글자를 추출한다.
그러면 'HELLO ORACLE'에서 진한 색 글자만 추출된다.
- 뒤에서부터 문자열 추출
SELECT SUBSTR('HELLO ORACLE', -5, 3)
FROM DUAL;
뒤에서 다섯 번째부터 순서대로 3글자를 추출한다.
그러면 'HELLO ORACLE'에서 진한 색 글자만 추출된다.
- 여자 직원만 뽑아내기
SELECT EMP_NAME, EMP_NO
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) IN('2', '4');
여자 직원만 뽑으려면 주민등록번호가 필요하다.
주민등록번호에서 성별을 나타내는 부분은 뒷자리 첫 번째 숫자인데, 이는 하이픈을 포함해서 8번째 문자열이다.
SUBSTR로 문자열을 추출하는데, 처음 위치를 8로 놓고, 이 한 글자만 추출하면 되므로 추출할 문자 개수는 1로 놓으면 된다.
여기까지만 하면 성별 구분 없이 모두 출력되므로 해당 문자열에 '2'나 '4'가 들어있는지 확인해야 하는데, 이때 IN을 사용하여 여자만 추출해내면 된다.
(4) LPAD / RPAD
: LPAD / RPAD(문자열, 최종 반환할 문자 길이(BYTE), 덧붙일 문자) - 제시한 문자열에 덧붙일 문자를 왼쪽이나 오른쪽에 덧붙여서 최종 반환할 문자 길이 만큼 문자열을 반환한다.
- 결과값을 문자열(CHARACTER)로 반환한다.
- 덧붙일 문자는 생략 가능하다.(공백이 기본값 ' ' 이다.)
- 이메일 주소 오른쪽에 #을 붙여 반환하기
SELECT RPAD(EMAIL, 18, '#')
FROM DUAL;
RPAD는 오른쪽에 문자를 덧붙인다.
최종 반환할 문자 길이는 18이므로 이메일 주소 길이가 18이 안되면 모자란 만큼 #이 붙어서 반환되고, 이메일 주소 길이가 18과 같거나 더 길면 #이 덧붙여 나오지 않는다.
- 주민등록번호 뒷자리를 * 표시로 반환하기(첫 번째 자리 제외)
SELECT EMP_NAME "이름", RPAD(SUBSTR(EMP_NO, 1, 8), 14, '*') "주민등록번호"
FROM EMPLOYEE;
문자열 오른쪽에 *를 붙여야 하므로 RPAD를 사용해야 한다.
그런데 RPAD(EMP_NO, 14, '*')라고 하면 주민등록번호는 이미 문자 길이가 14이므로 *를 붙여서 반환할 수 없다. 그러면 뒷자리 첫 번째까지만 문자열을 반환하고 그 문자열 오른쪽부터 *를 붙이는 것이 방법이다.
먼저 특정 문자열을 추출할 수 있는 함수 SUBSTR을 사용한다.
SUBSTR로 하이픈 포함하여 첫 번째 문자부터 여덟 번째 문자까지 추출하면 되므로 'SUBSTR(EMP_NO, 1, 8)'로 주민등록번호 뒷자리 첫 번째 자리까지 추출해냈다. 그 다음 추출한 이 문자열 오른쪽부터 총 14자리 될 때까지 *를 붙이면 끝이다. 'RPAD(SUBSTR(EMP_NO, 1, 8), 14, '*')'
(5) LTRIM / RTRIM
: LTRIM / RTRIM(문자열, 제거할 문자) - 문자열의 왼쪽이나 오른쪽에서 제거할 문자들을 찾아 제거한 나머지 문자를 반환한다.
- 결과값은 문자열(CHARACTER)로 반환한다.
- 제거할 문자는 생략 가능하다.(생략 시 기본값 ' ' 공백 삭제)
- 왼쪽 공백 제거하기
SELECT LTRIM(' TIS TORY ')
FROM DUAL;
LTRIM은 왼쪽 공백을 제거하여 반환한다.
- 오른쪽 문자열 제거하기
SELECT RTRIM('000123450000', '0')
FROM DUAL;
RTRIM은 오른쪽 문자열을 제거하여 반환한다.
오른쪽부터 순차적으로 0을 제거하면 5와 만났을 때 실행이 멈추고 반환된다.
(6) TRIM
: TRIM(BOTH / LEADING / TRAILING '제거할 문자' FROM '문자열') - 문자열의 양쪽/앞쪽/뒷쪽에 있는 특정 문자를 제거한 나머지 문자열을 반환한다.
- BOTH / LEADING / TRAILING: 생략 시 기본값은 BOTH이다.
- 문자 양쪽 다 제거 후 반환하기
SELECT TRIM('A' FROM 'AAABBCCDDAA')
FROM DUAL;
BOTH / LEADING / TRAILING를 생략하면 기본값은 BOTH이므로 양쪽에 있는 모든 A들이 제거된 후 반환되었다.
- 문자 왼쪽만 제거한 후 반환하기(LTRIM과 유사)
SELECT TRIM(LEADING 'A' FROM 'AAABBCCDDAA')
FROM DUAL;
앞에서부터 A를 제거하는데 A가 아닌 문자를 만나면 실행을 멈추고 반환한다.
만약 LEADING이 아니라 TRAILING이라면 뒤에서부터 A를 제거하여 'AAABBCCDD'가 반환된다.
(7) LOWER / UPPER / INITCAP
- LOWER(문자열): 모든 문자열을 소문자로 변경한다.
- UPPER(문자열): 모든 문자열을 대문자로 변경한다.
- INITCAP(문자열): 첫 글자만 대문자로 변경한다.
- 결과값은 문자열(CHARACTER)로 반환한다.
SELECT LOWER('HELLO WORLD!'), UPPER('hello world!'), INITCAP('hello world!')
FROM DUAL;
INITCAP은 'hello world!'를 'helloworld!'로 두 단어를 붙이면 한 단어로 인식한다.
그러나 단어 사이에 특수문자를 넣으면 각각의 단어로 인식한다.('hello$world!' →'Hello World!')
(8) CONCAT
: CONCAT(문자열1, 문자열2) - 전달된 문자열 두 개를 하나의 문자열로 합쳐서 반환한다.
- 결과값은 문자열(CHARACTER)로 반환한다.
SELECT CONCAT('안녕 ', '클레오파트라!')
FROM DUAL;
문자열 두 개만 쓸 수 있어서 문자열을 더 추가하면 오류가 발생한다.
- 문자열 추가하여 반환하기
SELECT CONCAT(CONCAT('안녕 ', '클레오파트라'), '세젤포!')
FROM DUAL;
CONCAT은 문자열 두 개만 쓸 수 있는데, 문자열을 더 추가하고 싶으면 기존 CONCAT의 결과값을 새로운 CONCAT에서 문자열1로 받고, 문자열2를 추가하면 된다.
(9) REPLACE
: REPLACE(문자열, 찾을 문자, 바꿀 문자) - 문자열로부터 찾을 문자를 찾아서 바꿀 문자로 바꾼 문자열을 반환한다.
- 결과값은 문자열(CHARACTER)로 반환한다.
SELECT REPLACE('클레오파트라', '오', '5')
FROM DUAL;
'클레오파트라' 라는 문자열에서 '오'를 '5'로 바꾼다.
- 직원의 이름과 이메일을 반환할 때 이메일의 @ 이후의 주소를 GMAIL.COM으로 변경하기
SELECT EMP_NAME, REPLACE(EMAIL, SUBSTR(EMAIL, INSTR(EMAIL, '@') + 1), gmail.com)
FROM EMPLOYEE;
이메일 @ 이후의 주소면 SITE 부분이다.
SITE 부분을 추출하려면, 우선 INSTR로 @의 위치값을 구한 뒤 SUBSTR으로 @의 위치값에 +1을 해서 SITE의 위치값부터 끝까지 문자가 추출되도록 해야 한다.
그 다음 SITE 부분이 추출되면, 이 부분을 REPLACE에서 찾을 문자로 넣고 gmail.com을 바꿀 문자로 넣으면 왼쪽의 결과값이 나온다.