[Oracle] SingleRow_String

단일행 함수 single-row function vs 그룹 함수 aggregate function

단일행 함수는 테이블 데이터에 여러 개(record)에 '각각 적용' 되어 산출된다

그룹 함수는 여러 개(record)를 동시에 입력 받아 '한 개의 결과값' 산출된다

 

단일행 함수 종류

문자 / 숫자 / 날짜 / 변환 / 일반 함수

 

문자 String 관련 함수

INITCAP, LOWER, UPPER, LENGTH, LENGTHB, CONCAT, SUBSTR, SUBSTRB, INSTR, INSTRB

SELECT INITCAP('abcd') FROM dual; dual은 oracle에서 제공하는 단일 행, 단일 컬럼 더미테이블이다

* database 에서는 인덱스가 대체로 1부터 시작한다!!

* 문자열 바이트 크기에 있어 인코딩이 만약 UTF-8로 되어있으면 한글은 '한글자' 당 '3byte'로 계산한다

INITCAP	첫글자만 대문자로 변환	INITCAP('abcd') -> Abcd
LOWER	소문자 변환	LOWER('ABCD') -> abcd
UPPER	대문자 변환	UPPER('abcd') -> ABCD
LENGTH	문자열 길이	LENGTH('한글') -> 2
LENGTHB	문자열 길이 바이트	LENGTHB('한글') -> 6 (UTF-8)
CONCAT	문자열 결합	CONCAT('A', 'B') -> AB
SUBSTR	특정 문자열 추출	SUBSTR('ABC', 1, 2) -> AB
SUBSTRB	특정 바이트 추출	SUBSTRB('한글', 1, 2) -> 한
INSTR	특정 문자 위치 추출	INSTR('A*B#', '#') -> 4
INSTRB	특정 문자 위치 바이트 추출	INSTRB('한글로', '로') -> 5

LPAD	주어진 문자열에서 왼쪽으로 특정 문자 채움	LPAD('love', 6, '*') -> **love
RPAD	주어진 문자열에서 오른쪽으로 특정 문자 채움	RPAD('love', 6, '*') -> love**
LTRIM	주어진 문자열에서 왼쪽의 특정 문자 삭제	LTRIM('*love', '*') -> love
RTRIM	주어진 문자열에서 오른쪽 특정 문자 삭제	RTRIM('love*', '*') -> love
REPLACE	문자열 치환	REPLACE('AB', 'A', 'E') -> EB

 

 

본격적으로 QUERY를 만들기에 앞서 ㅡ

query문을 만들 때는 주어진 과제에 대해 다음과 같이 구분하여 query문을 구성할 줄 알아야한다

- 어느 테이블에서 자료 추출을 해야 하는가? from, join 절

- 최종 출력 column과 이름(alias)는 무엇인가? select 절

- 출력 조건이 무엇인기? -> where / order by / group by / having 절

 

 

INITCAP 함수

첫글자만 대문자로 출력하고 나머지는 전부 소문자로 출력하는 함수

SELECT 'pretty girl', INITCAP('PRETTY GITL')
FROM dual;

SELECT INITCAP (id) "ID"
FROM t_student WHERE deptno1 = 201;

 

LOWER / UPPER

LOWER : 입력되는 값을 전부 소문자로 변경하여 출력

UPPER : 입력되는 값을 전부 대문자로 변경하여 출력

SELECT lower(id), upper(id)
FROM t_student
WHERE deptno1 = 201;

 

LENGTH / LENGTHB

입력된 문자열의 길이(바이트 수)를 계산해주는 함수

* 인코딩 세팅값에 따라 byte 수가 다르다. UTF-8을 사용하기에 한글 1글자 당 3byte로 나온다

SELECT name "이름", id, length(id) "글자수"
FROM t_student
WHERE length(id) >= 9; -- 단일행 함수는 조건절에서 사용이 가능하다

SELECT name "이름", length(name) "문자개수", lengthb(name) "바이트"
FROM t_student
WHERE deptno1 = 201;

 

 

concat 함수 ( || 연산자와 동일)

합쳐져서 하나의 column으로 나온다

SELECT concat(name, position) "교수님명단", name || position
FROM t_professor;

 

substr 함수

구문 : substr('문자열' 또는 컬럼명, 시작위치, 추출할 글자수)

문자열에서 특정 길이의 문자를 추출할 때 사용하는 함수

음수 인덱스 지원한다

★ 시작 인덱스가 1부터 시작한다 (oracle 문자열 인덱스는 1부터 시작한다)

SELECT substr('ABCDE', 2, 3) FROM dual;
SELECT substr('ABCDE', 20, 3) FROM dual; -- 에러가 아닌 NULL이 뜬다
SELECT substr('ABCDE', -2, 3) FROM dual;

-- 단일함수이기에 조건절에서도 쓸 수 있다
SELECT name, substr(jumin, 1, 6) "생년월일"
FROM t_student WHERE deptno1 = 101;

 

instr 함수

구문 : INSTR('문자열', 찾는 문자열, 시작위치, 몇번째 등장?);

주어진 문자열이나 컬럼에서 특정 글자의 위치를 찾아주는 함수이다

SELECT INSTR('A*B*C*', '*', 1, 1) FROM dual; --2
SELECT INSTR('A*B*C*', '*', 1, 2) FROM dual; --4
SELECT INSTR('A*B*C*', '*', -4, 1) FROM dual; -- 2 음수위치부터 찾기. 시작하면 음의 방향으로 검색 진행
SELECT INSTR('A*B*C*', '*', -4, 2) FROM dual; -- NULL이 아닌 0을 리턴한다 c언어스러운
SELECT INSTR('A*B*C*', '*') FROM dual; -- 뒤에 없어도 DEFAULT 값으로 1, 1 나온다

 

 [실습] #4111
 t_student 테이블 : 1전공이 101 인 학생의 이름과 전화번호, 지역번호를 출력하세요.
지역번호는 숫자만!  / substr(), instr() 사용

SELECT name, tel, substr(tel, 1, instr(tel, ')') -1) AS 지역번호
FROM t_student
WHERE deptno1 = 101;

 

LPAD / RPAD

문구 : LPAD('문자열', 자릿수, 넣을 문자)

* 지금은 좌측정렬로 되어 있는데 실제 출력되는 콘솔에서 우측정렬처럼 보이게 하고 싶으면 PADDING 문자를 빈 문자열로 주면 된다

SELECT 'abcd', LPAD('abcd', 10 ''#') 
FROM dual;

 

 [실습]  #4113

* 한글은 2글자(2칸)로 인식한다(영어보다 폭이 더 크니까) C언어의 흔적

SELECT LPAD(dname, 10, 1234567890) "LPAD"
FROM t_dept2;

 

LTRIM / RTRIM() 함수

SELECT 
	LTRIM('      슈퍼슈퍼슈가맨') AS LTRIM, 
	RTRIM('슈퍼슈퍼슈가맨      ') AS RTRIM,
	TRIM('슈퍼슈퍼슈가맨') AS TRIM
FROM dual;

* 두번째 매개변수에 지울 문자열을 넣을 수 있다

SELECT LTRIM('슈퍼슈퍼맨', '슈퍼') AS LTRIM
FROM dual;

 

replace() 함수

구문 : replace('문자열', '문자1', '문자2')

* 공백 제거 (문자열 안의 공백을 제거, trim은 좌우 공백 제거)

SELECT 'ab cd ef',
	REPLACE('ab cd ef', ' ', '')
FROM dual;

 

[실습] #4121

SELECT name, tel, replace(tel, substr(tel, instr(tel, ')') + 1, 3), '###') AS 전화번호
FROM T_STUDENT 
WHERE DEPTNO1 = 102