[SQL 06] 단일 행 함수 - 문자 함수

샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.

No. 01
단일 행 함수란?

단일 행 함수는 모든 행에 대하여 각각 적용되어 행의 개수와 동일한 결과를 제공한다.

단일 행 함수의 종류

단일 행 함수의 기능

• 함수의 중첩이 가능하다.
• 함수의 결과 값은 입력 값마다 하나씩 리턴 된다.
• 함수의 인자는 컬럼명, 수식, 변수 등을 사용할 수 있다.
• 데이터를 조작한다.

No. 02
문자 함수

문자 함수는 문자를 인자로 사용하고 결과 값도 문자를 반환하는 함수이다.

▣ 대소문자 조작 함수

함수명	기 능	사 용
LOWER	문자열을 소문자로 변환한다.	LOWER(문자열)
		LOSER('SQL Course') → sql course
UPPER	문자열을 대문자로 변환한다.	UPPER(문자열)
		UPPER('SQL Course') → SQL COURSE
INITCAP	첫 문자만 대문자로 변환하고,  나머지는 소문자로 변환한다.	INITCAP(문자열)
		INITCAP('SQL Course') → Sql Course

◈ 예제

select first_name, lower(first_name), upper(first_name), initcap(first_name)

from employees;

▶ 이름이 'Amit'인 사원의 정보를 검색할 때

select employee_id, first_name, department_id, salary

from employees

where first_name = 'amit';

no rows selected

select employee_id, first_name, department_id, salary

from employees

where lower(first_name) = 'amit';

HR 스카마의 테이블에는 입력 원칙을 가지고 문자 데이터가 입력되어 있지만, 실제 현장에서는 입력 원칙이 정해지지 않아 데이터를 입력하는 사람 마음대로 입력된 데이터가 있을 가능성이 있다.

입력된 데이터에 대·소문자가 섞여 있는 경우 문자열을 검색하기 위하여 대·소문자 조작 함수를 사용한다. 하지만, 가장 좋은 방법은 입력 작업을 할 때 원칙을 정해서 대·소문자를 구별하여 함수를 사용하지 않도록 하는 것이 좋다.

▣ 문자 조작 함수

함수명	기능 & 사용
CONCAT	두 개의 문자열을 연결한다. (연결 연산자와 비슷한 기능)
	CONCAT(문자열1, 문자열2)  CONCAT('Hello', 'World') → HelloWorld
SUBSTR	문자열 내에서 지정된 위치의 문자열을 반환한다.
	SUBSTR(문자열, 시작위치, 출력문자 개수)  SUBSTR('HelloWrold',2,5) → elloW
LENGTH	문자열의 길이를 반환한다.
	LENGTH(문자열)  LENGTH('HelloWorld') → 10
INSTR	지정된 문자의 위치를 리턴한다.
	INSTR(문자열, 검색문자, [시작위치, [횟수]])  INSTR('HelloWorld','o') → 5
TRIM	접두어나 접미어를 잘라낸다.
	TRIM([leading | trailing | both] 제외 문자 FROM 문자열)  TRIM('H' FROM 'HelloWorld') → elloWorld
LPAD, RPAD	지정된 문자열의 길이만큼 빈부분에 문자를 채운다.
	LPAD(문자열, 출력길이, 채울문자), RPAD(문자열, 출력길이, 채울문자)  LPAD('World', 10, '*') → *****World,  RPAD('World', 10, '*') → World*****

◈ 예제

▶ CONCAT

select first_name, last_name, concat(first_name, last_name)

from employees;

 TIP  동일한 작업을 할 경우 함수보다 연결 연산자(||)의 성능이 더 좋다.

▶ SUBSTR

select first_name, substr(first_name,3), substr(first_name,-3), substr(first_name,2,5), substr(first_name,-5,3)

from employees;

시작위치가 '-'인 경우는 문자열의 끝에서부터 위치를 정한다.

예) -5 : 끝에서 다섯 번째

▶ LENGTH

select first_name, length(first_name)

from employees;

▶ INSTR

select instr('database', 'a'), instr('database', 'a', 3), instr('database', 'a', 1, 3)

from dual;

시작위치, 횟수를 쓰지 않을 경우 기본값은 시작위치=1, 횟수=1

instr('database', 'a') : 'database'라는 문자열에서 첫 번째 'a'의 위치

instr('database', 'a', 3) : 'database'라는 문자열에서 세 번째부터 검색한 'a'의 위치

instr('database', 'a', 1, 3) : 'database'라는 문자열에서 첫 번째부터 검색한 세 번째 'a'의 위치

▶ TRIM

select trim('r' from 'register'), trim(leading 'r' from 'register'), trim(trailing 'r' from 'register')

from dual;

기본값 : both

trim('r' from 'register') : 'register'라는 문자열의 'r'이라는 접두어와 접미어를 잘라낸다.

trim(leading 'r' from 'register') : 'register'라는 문자열에서 'r'이라는 접두어를 잘라낸다.

trim(tailing 'r' from 'register') : 'register'라는 문자열에서 'r'이라는 접미어를 잘라낸다.

문자열의 접두어와 접미어에 제외 문자가 없으면 문자열 그대로를 출력한다.

제외 문자는 한 글자만 가능하다. 잘못된 예) trim('re' from 'register')

▶ LPAD, RPAD

select first_name, rpad(first_name, 10, '#'), lpad(first_name, 10, '#')

from employees;

▣ 문자 치환 함수

함수명	기능 & 사용
TRANSLATE	문자 단위 치환된 값을 리턴한다.
	TRANSLATE(문자열, 검색문자, 치환문자)  TRANSLATE('hello', 'e', '*') → h*llo
REPLACE	문자열 단위 치환된 값을 리턴한다.
	REPLACE(문자열, 시작위치, 출력문자 개수)  REPLACE('hello', 'el', '**')  → h**lo

◈ 예제

select translate('hello world', 'lo', '**') translate, replace('hello world', 'lo', '**') replace

from dual;

TRANSLATE는 'l'과 'o' 각각의 문자를 '*'로 치환하고,

REPLACE는 'lo'라는 문자열을 '**'문자열로 치환한다.

select translate('hello world', 'lo', '$*') translate

from dual;

문자 'l'은 '$'로 치환되고, 문자 'o'는 '*'로 치환

 TIP 

DUAL 테이블이란?

SELECT문에는 반드시 FROM절을 기술해야 한다.

예를 들어 10*20은 어느 테이블에서도 데이터를 가져오는 것이 아니기 때문에 FROM절에 기술할 테이블이 없다.

이런 경우 사용할 수 있는 DUMMY 테이블이 DUAL이다.

select 10*20

from dual;