SQL 13 - 단일 행 함수 : 문자 함수

단일 행 함수의 종류
- 문자 함수
- 숫자 함수
- 날짜 함수
- 변환 함수
- 일반 함수

 

▶ 문자 함수

* 대소문자 변환 함수

 

예제 1. ERP 부서가 있는 지역을 검색한다.

SELECT loc ERP_부서_지역
FROM dept
WHERE LOWER(dname)='erp';

ERP_부서_지역
------------------
서울

 

 

예제 2. 'XX 부서는 XX에 위치합니다.' 형식으로 부서 정보를 검색한다.

SELECT INITCAP(dname) || ' 부서는 ' || loc || '에 위치합니다.' 부서_위치
FROM dept;

부서_위치
-------------------------------------
총무 부서는 서울에 위치합니다.
회계 부서는 서울에 위치합니다.
Erp 부서는 서울에 위치합니다.
Isp 부서는 부산에 위치합니다.
Itea 부서는 광주에 위치합니다.
Crm 부서는 대전에 위치합니다.
Pos 부서는 에 위치합니다.

 

* TRIM(ename) : ename 컬럼의 앞 뒤ㅣ 공백 제거

 

 

예제 3. 부서의 명과 위치를 하나의 컬럼으로 검색한다.

SELECT CONCAT(dname,' '|| loc) CONCAT
FROM dept;

CONCAT
----------------
총무 서울
회계 서울
ERP 서울
ISP 부산
ITEA 광주
CRM 대전
POS

 

예제 4. 부서명과 문자열의 길이를 출력한다.

SELECT dname, LENGTH(dname), LENGTHB(dname)
FROM dept;

DNAME   LENGTH(DNAME) LENGTHB(DNAME)
------- ------------- --------------
총무                2              6
회계                2              6
ERP                 3              3
ISP                 3              3
ITEA                4              4
CRM                 3              3
POS                 3              3

 

예제 5. substr 함수를 이용해서 컬럼에 일부 내용만을 검색한다.

SELECT ename, SUBSTR(ename,2),
SUBSTR(ename,-2),
SUBSTR(ename,1,2),
SUBSTR(ename,-2,2)
FROM emp;

ENAME      SUBSTR(ENAME,2)  SUBSTR(ENAME,-2) SUBSTR(ENAME,1,2 SUBSTR(ENAME,-2,
---------- ---------------- ---------------- ---------------- ----------------
안영희     영희               영희             안영             영희

 

예제 6. 다양한 방법으로 'a'가 나오는 위치를 출력한다.

SELECT INSTR('database','a'),
	INSTR('database','a',3),
	INSTR('database','a',1,3)
FROM dual;

INSTR('DATABASE','A') INSTR('DATABASE','A',3) INSTR('DATABASE','A',1,3)
--------------------- ----------------------- -------------------------
                    2                       4                         6

 

예제 7. TRIM 함수를 이용 다양한 방법으로 문자열을 검색한다.

SELECT TRIM('남' from '남기남'),
TRIM(leading '남' from '남기남'),
TRIM(trailing '남' from '남기남'),
TRIM(' 남기남 ')
FROM dual;

TRIM(' TRIM(LEADING TRIM(TRAILIN TRIM('남기남')
------ ------------ ------------ ------------------
기     기남         남기         남기남

 

예제 8. 이름과 급여를 각각 10 컬럼으로 검색한다.

SELECT RPAD(ename, 10, '*'), LPAD(sal, 10, '*')
FROM emp;

RPAD(ENAME,10,'*')  LPAD(SAL,10,'*')
------------------  -------------------
안영희****           안영숙****
******4800           ******3900

 

예제 9. 부서명의 마지막 글자를 제외하고 검색한다.

SELECT dname, SUBSTR(dname, 1, LENGTH(dname)-1) dname
FROM dept;

DNAME   DNAME
------- -------
총무    총
회계    회
ERP     ER
ISP     IS
ITEA    ITE
CRM     CR
POS     PO

 

 

* 문자 치환 함수

 

 

예제 10. 형식이 비슷한 TRANSLATE와 REPLACE를 동일한 치환을 통해 비교해 보자

SELECT TRANSLATE('World of Warcraft', 'Wo', '--') Translate,
REPLACE('World of Warcraft', 'Wo', '--') Replace
FROM dual;

TRANSLATE                          REPLACE
---------------------------------- ----------------------------------
--rld -f -arcraft                  --rld of Warcraft

 

실습 (모두 단일 행 함수를 이용한다.)

1. 이름이 두 글자인 학생의 이름을 검색한다.

SELECT sno 학번, sname 이름
FROM student
WHERE LENGTH(sname) = 2;

학번    이름
------- ----------
925301  김환
945604  권현
938204  최현

2. '강' 씨 성을 가진 학생의 이름을 검색한다.

SELECT sno 학번, sname 이름
FROM student
WHERE SUBSTR(sname, 1, 1) = '강';

학번    이름
------- ----------
915605  강은혜
915601  강태용
925602  강아영

3. 교수의 지위를 한글자로 검색한다.(ex. 조교수 → 조)

SELECT pno 교수번호, pname 이름, SUBSTR(orders, 1, 1) 지위
FROM professor;

교수번호  이름       지위
--------- ---------- -------
1001      송강       정
1004      시진영     부
1006      장청아     부
1007      이초아     조
1008      문규식     조
1010      이규진     정
1009      이준영     정

4. 일반 과목을 기초 과목으로 변경해서 모든 과목을 검색한다. (ex. 일반화학 → 기초화학)

SELECT cno 과목번호, REPLACE(cname, '일반', '기초') 과목이름
FROM course;

과목번호  과목이름
--------- ------------
1211      기초화학실험
1212      기초화학
1213      유기화학
1214      무기화학
1216      환경화학
1217      고분자화학
1218      생화학

과목번호  과목이름
--------- ------------
1220      기초물리
1223      자기학
1226      양자물리학
1227      이학수학
1228      위상수학
2357      유기물리학
2358      식물학

5. 만일 입력 실수로 student 테이블의 sname 컬럼에 데이터가 입력될 때 문자열 마지막에 공백이 추가 되었다면 검색할 때 이를 제외하고 검색하는 SELECT문을 작성한다.

SELECT sno 학번, TRIM(trailing FROM sname) 이름, 
LENGTH(sname) 공백제거전, LENGTH(TRIM(trailing FROM sname)) 공백제거후
FROM student;

학번    이름       공백제거전 공백제거후
------- ---------- ---------- ----------
915301  정동상              3          3
905301  유태지              3          3
905302  정욱상              3          3
915303  정욱주              3          3
923903  정남윤              3          3
923904  한현석              3          3
933901  김용서              3          3

6. 직원의 연봉을 10자리로 검색한다. 단 공백은 임의의 채움 문자로 채워 넣는다.

SELECT eno 사번, ename 이름, LPAD(sal*12+NVL(comm,0), 10, '#') 연봉
FROM emp;

사번    이름       연봉
------- ---------- ---------------
0201    안영숙     #####48800
0202    손하늘     #####43100
0301    이승철     #####40800
0302    박선경     #####39600
1001    문시현     #####54520
1002    김주란     #####49530

7. 학생의 이름을 검색한다. 단 성이 '심'인 학생은 성을 '사마' 로 바꾸어 검색한다.

SELECT sno 학번, sname 원래이름, REPLACE(sname, '심', '사마') 변경이름
FROM student;

학번    원래이름   변경이름
------- ---------- ----------
925603  최지현     최지현
925604  김가원     김가원
935601  독고현아   독고현아
935602  윤희주     윤희주
935604  권석현     권석현
935605  한지우     한지우
945601  심정용     사마정용

학번    원래이름   변경이름
------- ---------- ----------
913904  이호란     이호란
923901  서윤훈     서윤훈
923902  김호야     김호야
914503  김대현     김대현
914504  심빈경     사마빈경
948201  이창지     이창지
948202  김성지     김성지