[Oracle] 함수? 문자열 관련 함수 정리

TIL: 스스로 이해한 것을 바탕으로 정리하였기에 오류가 있을 수 있습니다. 틀린 부분은 댓글로 알려주시면 감사하겠습니다.

 

 

함수 FUNCTION

 

전달된 값을 읽어서 계산한 결과를 반환함

 

단일행 함수 :  N개의 값을 읽어서 N개의 결과를 리턴 (매 행마다 함수 실행후 결과 반환)

그룹 함수 : N개의 값을 읽어서 1개의 결과를 리턴 (하나의 그룹별로 함수 실행 후 결과 반환)

 

리턴하는 결과값의 갯수가 다르기 때문에 단일행 함수와 그룹 함수를 함께 사용할 수 없다

 

 

 

단일행 함수

 

1. 문자열에 관련된 함수

문자열에 관련된 함수		반환 타입
LENGTH( STR )	해당 전달된 문자열의 글자수 반환	NUMBER
LENGTHB( STR )	해당 전달된 문자열의 바이트수 반환	NUMBER
INSTR( STR, '문자' [, 찾을위치시작값, [순번] ] )	문자열로부터 특정 문자의 위치값 반환	NUMBER
SUBSTR( STR, POSITION, [LENGTH] )	특정 문자열을 추출해서 반환	CHARACTER
LPAD / RPAD( STR, 반환할문자길이, [덧붙일문자] )	문자를 통일감있게 보여주고자 사용	CHARACTER
LTRIM / RTRIM( STR, [제거시키고자하는문자] )	문자열의 (왼/오)에서 제거할 문자를 제거하여 반환	CHARACTER
TRIM( [B/L/T] [제거할문자 FROM] STR )	문자열의 (앞/뒤/양쪽)에 특정문자를 제거하여 반환	CHARACTER
LOWER / UPPER / INITCAP ( STR )	(소문자/대문자/앞글자대문자)로 바꾸어 반환	CHARACTER
CONCAT( STR, STR )	전달된 두 문자열을 하나로 합친 결과 반환	CHARACTER
REPLACE(STRING, STR1, STR2)	STRING으로부터 STR1을 STR2로 바꾼값 반환	CHARACTER

 

* LENGTHB

한글 : 'ㄱ'  'ㅏ'  '가'  '강'  -> 한 글자당 3BYTE

숫자, 영문, 특수문자 : '!'  '~'  '1'  'A'  -> 한 글자당 1BYTE

 

 

 

* INSTR

찾을위치의 시작값 

 1 : 앞에서부터 (생략시 기본값)

-1 : 뒤에서부터 찾음

 

ex. 

SELECT INSTR('AABBCCDDAABB', 'B', 1, 2) FROM DUAL;

-> 'AABBCCDDAABB'의 두번째 B를 앞에서부터 찾는다. = 4 

 

 

 

* SUBSTR

STR : '문자열' 또는 문자타입 컬럼

POSITION : 문자열을 추출할 시작위치값

LENGTH : 추출할 문자 갯수 (생략시 끝까지 추출)

 

ex.

SELECT SUBSTR('DOGPAWSTISTORY', 8) FROM DUAL;

-> 8번째부터 끝까지 추출 = TISTORY

 

SELECT SUBSTR('DOGPAWSTISTORY', 8, 3) FROM DUAL;

-> 8번째부터 3개의 글자 추출 = TIS

 

SELECT SUBSTR('DOGPAWSTISTORY', -8, 4) FROM DUAL;

-> 뒤에서 8번째 부터 4개의 글자 = PAWS

 

ex. 남자 사원만 조회하기

SELECT EMP_NAME

FROM EMPLOYEE

WHERE SUBSTR(EMP_NO, 8, 1) = '1' OR SUBSTR(EMP_NO, 8, 1) = '3';         또는

WHERE SUBSTR(EMP_NO, 8, 1) IN ('1', '3');

-> 1 또는 2 추출

 

 

 

 

* LPAD / RPAD

제시한 문자열에 임의의 문자를 왼쪽또는 오른쪽에 덧붙여서 문자열을 반환

오른쪽 또는 왼쪽으로 정렬시킬 수 있다.

 

덧붙일문자 생략시 공백으로 채워짐

 

ex. 주민번호 112233-1****** 형식으로 조회하기

SELECT RPAD('112233-1', 14, '*') FROM DUAL;

 

 

SELECT EMP_NAME, RPAD( SUBSTR(EMP_NO, 1, 8), 14, '*')

FROM EMPLOYEE;

-> EMP_NO의 첫번째 문자부터 8번째까지 문자추출 후 14의 나머지는 '*' 로 채우기

 

 

 

 

* LTRIM / RTRIM

ex. 

SELECT LTRIM('000123123000123', '0') FROM DUAL;

--> 123123000123 추출

 

SELECT LTRIM('ACBCACDDD', 'ABC') FROM DUAL;

--> DDD 추출 -> ABC는 단순 나열이기 때문에 순서가 맞지않아도 제거가능

 

 

 

 

* TRIM

1. 생략시 양쪽 문자열 제거

ex.

SELECT TRIM('   DOG   ') FROM DUAL;

-> 'DOG'

 

ex.

SELECT TRIM('Z' FROM 'ZZZDOGZZZ') FROM DUAL;

-> 'DOG'

 

2. BOTH : 양쪽

SELECT TRIM (BOTH 'Z' FROM 'ZZZDOGZZZ') FROM DUAL; 

-> 'DOG'

 

3. LEADING : 앞

SELECT TRIM (LEADING 'Z' FROM 'ZZZDOGZZZ') FROM DUAL;

-> 'DOGZZZ'

 

4. TRAILING : 뒤

SELECT TRIM (TRAILING 'Z' FROM 'ZZZDOGZZZ') FROM DUAL;

-> 'ZZZDOG'

 

 

 

* LOWER / UPPER / INITCAP

SELECT LOWER('HELLO WORLD') FROM DUAL;

-> 'hello world'

 

SELECT UPPER('hello world') FROM DUAL;

-> 'HELLO WORLD'

 

SELECT INITCAP('HELLO WORLD') FROM DUAL;

-> 'Hello World'

 

 

 

* CONCAT

SELECT CONCAT('가나다', 'ABC') FROM DUAL;

-> '가나다ABC'

 

두개이상의 문자열을 합치고 싶을 땐 연산자로 사용

SELECT '가나다' || 'ABC' || 'DEF' FROM DUAL;

 

 

 

* REPLACE

SELECT REPLACE ('서울시 강남구 역삼동', '역삼동', '삼성동') FROM DUAL;

-> '서울시 강남구 삼성동'