[SQL] 08. (1) 단일행 문자 함수

 

 

0) 단일행 함수란?

 ① 한 번에 하나씩 처리하는 함수

 ② 문자함수, 숫자함수, 날짜함수, 변환함수(묵시적 데이터형 변환/ 명시적 데이터형 변환), 일반함수

 

 

   (1) 문자함수 

 

INTCAP	입력값의 첫 글자만 대문자로 변환
LOWER	입력 값을 전부 소문자로 변환
UPPER	입력값을 전부 대문자로 변환
LENGTH	입력된 문자열의 길이 값을 출력
LENGTHB	입력된 문자열의 길이의 바이트 값을 출력
CONCAT	두 문자열을 결합해서 출력, || 연산자와 동일
SUBSTR	주어진 문자에서 특정문자만 추출
SUBSTRB	주어진 문자에서 특정바이트만 추출
INSTR	주어진문자에서 특정 문자의 위치 추출
LPAD	주어진 문자열에서 왼쪽으로 특정문자를 채움
RPAD	주어진 문자열에서 오른쪽으로 특정문자를 채움
LTRIM	주어진 문자열에서 왼쪽의 특정 문자를 삭제
RTRIM	주어진 문자열에서 오른쪽의 특정 문자를 삭제
REPLACE	주어진 문자열에서 A를 B로 치환
REGEXP_REPLACE	주어진 문자열에서 특정패턴를 찾아 치환
REGEXP_INSTR	주어진 문자열에서 특정패턴의 시작위치를 찾아 반환
REGEXP_SUBSTR	주어진 문자열에서 특정패턴을 찾아 반환
REGEXP_LIKE	주어진 문자열에서 특정패턴을 찾아 반환
REGEXP_COUNT	주어진 문자열에서 특정패턴의 횟수를 반환

   

 

   1) INTCAP() 함수 

       ① 영어에서 첫 글자만 대문자로 출력하고 나머지는 전부 소문자로 출력

       ② 중간에 공백이 있을 경우 공백 다음을 첫글자로 생각하여 대문자로 출력 함 

INTCAP(문자열 또는 컬럼명)

SELECT name, INTCAP(name)"INITCAP"
FROM professor
WHERE DEPTNO = 101;

NAME			INITCAP
-------------------- ---------------------
Audie Murphy		Audie Murphy
Angela Bassett		Angela Bassett
Jessica Lange		Jessica Lange

 

   2) LOWER(), UPPER() 함수 

        ① LOWER() : 함수에 입력되는 값을 전부 소문자로 변경하여 출력

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

LOWER(문자열 또는 컬럼명)

UPPER(문자열 또는 컬럼명)

SELECT ename, LOWER(ename)"LOWER", UPPER(ename)"UPPER"
FROM emp 
WHERE deptno = 10;

ENAME	LOWER	UPPER
-----------------------
CLARK	clark	CLARK
KING	king	KING
MILLER	miller	MILLER

 

    3) LEGNTH / LENGTHB 함수

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

        ② 영어일 경우  LEGNTH와 LENGTHB값이 동일하지만 한글을 저장할 경우 1글자당 2바이트를 사용

LENGTH(컬럼 또는 문자열) / LENGTR(컬럼 또는 문자열)

SELECT ename, LENGTH(ename) "LENGTH", LENGTHB(ename) "LENGTHB"
FROM emp 
WHERE deptno = 20;

ENAME 		LENGTH 		LENGTHB
--------------------------------------------------
SMITH		5		5
JONES		5		5
FORD		4		4

SELECT '홍길동' "NAME", LENGTH('홍길동') "LENGTH",
			LENGTH('홍길동')"LENGTHB"
FROM dual;

ENAME 		LENGTH 		LENGTHB
-------------------------------------------
홍길동		3		6

 

    4) CONCAT() 함수 

        ①  ||  연산자와 동일한 기능 

CONCAT('문자열 1', '문자열 2')

SELECT CONCAT(ename, job)
FROM emp 
WHERE deptno m = 10;

CONCAT(ENAME, JOB)
------------------------
CLARKMANAGER
KINGPRESIDENT
MILLERCLERK

 

   5) SUBSTR() 함수 

      ① 주어진 문자열에서 특정 길이의 문자만 골라낼 때 사용하는 함수

      ② 마이너스를 안 한 경우 : 왼쪽 → 오른쪽으로 검색하여 글자를 골라냄

      ③ 마이너스를 붙인 경우 : 오른쪽 → 왼쪽으로 검색을 한 후 왼쪽 → 오른쪽으로 글자 수를 세어 골라냄 

SUBSTR('문자열' 또는 컬럼명, 숫자, 숫자)

SELECT SUBSTR('abcde',3,2) "3 , 2"
	SUBSTR('abcde',-3,2) "-3 , 2"
    	SUBSTR('abcde',-3,4) "-3 , 2"
        FROM dual;
        

3.2		-3.2		-3.4
-----------------------------------------
cd		cd		cde

 

  6) SUBSTRB() 함수

     ① SUBSTR() 함수와 문법은 동일하며 차이점은 추출한 자릿수가 아니라 추출할 바이트 수를 지정하는 것 

     ② 영어일 경우 결과가 동일하지만 영어를 제외한 다른 글자는 결과가 다를 수 있음 

SELECT  '홍길동' "NAME", SUBSTR('홍길동', 1,2) "SUBSTR"
			SUBSTRB('홍길동', 1,2) "SUBSTRB"
            FROM dual;
            
NAME	SUBSTR	SUBSTRB
----------------------
홍길동	  홍길	  홍

 

  7) INSTR() 함수

       ① 주어진 문자열이나 칼럼에서 특정 글자의 위치(숫자)를 찾아주는 함수 

       ② 오라클 함수에서 position에 음수 값을 넣으면 문자열의 제일 마지막에서 음수의 절대값 만큼

          왼쪽으로 이동 후 왼쪽으로 검색을 시작함.

       ③ 주의할 점은 INSTR() 함수의 경우 position에 음수 값이 있으면 왼쪽으로 검색 후 왼쪽으로 계속 검색 함.

INSTR('문자열' 또는 칼럼, 찾는 숫자 , 시작위치, 몇번째인지(기본값은 1))

SELECT 'A-B-C-D', INSTR('A-B-C-D','-',1,3) "INSTR"
  2  FROM dual;

'A-B-C-      INSTR
------- ----------
A-B-C-D 	 6

SELECT 'A-B-C-D' , INSTR('A-B-C-D','-',3,1) "INSTR"
  2  FROM dual;

'A-B-C-      INSTR
------- ----------
A-B-C-D 	 4

SELECT 'A-B-C-D', INSTR('A-B-C-D','-',-3,1) "INSTR"
  2  FROM dual;

'A-B-C-      INSTR
------- ----------
A-B-C-D 	 4

 

    8) LPAD() 함수 

        ① LPAD : 왼쪽에서 부터 채운다

        ② 기존 데이터를 오른쪽에 두고 왼쪽에 빈 자리가 있을 경우 왼쪽을 특정 기호나 문자로 채움

LPAD('문자열' 또는 컬럼명, 자릿수, '채울 문자 ')

SELECT 	name, id, LPAD(id, 10, '*')
FROM student
WHERE deptno1 = 201;


NAME			ID			LPAD(id, 10, '*')
-----------------------------------------------------------------------
Demi Moore		Moore			*****Moore
Sean Connery		Connery			***Connery

 

   9) RPAD() 함수 

RPAD('문자열' 또는 컬럼명 자릿수, '채울 문자')

SELECT 	name, id, RPAD(id, 10, '*')"RPAD"
FROM emp
WHERE deptno1 = 10;


RPAD
------------------------------------------
CLARK-----
KING------
MILLER----

 

    10) LTRIM() 함수 

        ① LPAD, RPAD는 채우는 함수였다면 LTRIM, RTRIM 함수는 제거 함수 

        ② 제일 왼쪽의 특정 문자를 제거하는 함수 

LTRIM('문자열' 또는 컬럼명, '제거할 문자')

select enmae FROM emp 
WHERE deptno o= 10;

ENMAE
----------------
CLARK
KING
MILLER

select LTRIM(ename, 'C') 
FROM emp 
WHERE deptno o= 10;

LTRIM(ename, 'C') 
----------------
LARK
KING
MILLER

 

11) RTRIM() 함수

       ① 제일 왼쪽의 특정 문자를 제거하는 함수 

RTRIM('문자열' 또는 컬럼명, '제거할 문자')

select ename, RTRIM(ename, 'R') "RTRIM"
FROM emp 
WHERE deptno = 10;

ENAME		RTRIM
---------------------
CLARK		CLARK
KING		KING
MILLER		MILLE

 

12) REPLACE() 함수 

   ① 주어진 문자열이나 컬럼에서 문자1을 문자2로 바꾸어 출력하는 함수 

REPLACE('문자열' 또는 컬럼명, '문자 1', '문자2')

select ename, REPLACE(ename , SUBSTR(ename,1,2) , '**') "REPLACE"
FROM emp 
WHERE deptno = 10;

ENAME		REPLACE
---------------------
CLARK		**ARK
KING		**NG
MILLER		**LLER