[Oracle] 단일행함수

단일행함수 : 각 행마다 반복적으로 적용되어 입력받은 행의 개수만큼 결과를 반환하는 함수

 

문자 함수 : LENGTH, LENGTHB, SUBSTR, UPPER, LOWER, INSTR, LPAD, RPAD, LTRIM, RTRIM, REPLACE

1. LENGTH (문자 | 컬럼명)

: 주어진 컬럼 값/문자열의 길이(문자 개수) 반환

1-1. LENGTHB (문자열 | 컬럼명)

: 주어진 컬럼 값/문자열의 길이 (BYTE) 반환

한글일 경우 한 글자당 3byte 저장된다. 영어는 LENGTH와 LENGTHB 결과값의 차이가 없다.

2. UPPER / LOWER (문자열 | 컬럼명)

: 입력값을 전부 대문자/ 대문자로 바꾼다.

3. INITCAP (문자열 | 컬럼명)

: 입력값에서 앞글자만 대문자로 바꿔준다.

4. SUBSTR (문자열 | 컬럼명, 시작위치, [자를길이])

: 컬럼이나 문자열에서 지정한 위치로부터 지정한 문자열의 길이만큼 잘라서 리턴

자를 길이를 지정하지 않는 경우 시작위치로부터 맨 뒤까지 출력된다.

1) 결과값 : ME

SELECT
	SUBSTR('SHOWMETHEMONEY', 5, 2)
FROM DUAL;

5. INSTR(문자열 | 컬럼명, 찾으려는 문자(열), 검색 시작할 위치, [빈도])

: 지정한 위치(POSITION)부터 지정한 숫자번째(OCCURRENCE)로 나타나는 문자의 시작 위치를 반환(숫자로!)

INSTR('STRING' | 컬럼명,'STR', POSITION ,[OCCURENCE])

- STRING : 문자타입 컬럼 또는 문자열

- STR : 찾으려는 문자(열)

- POSITION : 찾을 위치 시작 값

- OCCURENCE : 검색된 STR의 순번(기본값 1)으로 STR이 여러개인 경우 말한다. 음수는 사용할 수 없다.

 

1) EMAIL컬럼에서 '@' 뒤에서부터 찾기

-1이기때문에 '@'를 뒤에서부터 찾지만 위치는 앞에서부터 센다! 

만약 '@'의 위치를 뒤에서부터 센다면 모든 VAL값이 9이지만 앞에서 부터 세기때문에 각기 다르게 나온다.

2) 문자열에서 4번째 이후에 있는 'B'를 찾기 --> 9 출력된다.

SELECT
	INSTR('AABAACAABBAA', 'B', 4) LOC
FROM DUAL;

3) 뒤에서부터 1번째 이후에있는 3번째 'B' 의 위치 앞에서부터 세어보면 3번째이므로 --> 3 출력된다. 

SELECT 
	INSTR('AABAACAABBAA','B',-1,3) LOC 
FROM DUAL;

 

6. LPAD / RPAD (문자열 | 컬럼명, 총문자열 길이, 덧붙일 특정문자)

: 주어진 컬럼 문자열에 왼쪽/오른쪽에 특정 문자를 덧붙여 길이 N의 문자열을 반환하는 함수

SELECT 
	LPAD(EMAIL, 20, '*')
FROM EMPLOYEE;

LPAD 결과

1) 주민등록번호 뒷자리 '*'로 출력하기 - SUBSTR로 주민번호 앞자리 떼오고 RPAD를 이용해 '*'로 14자리 채우기

SELECT
	RPAD(SUBSTR(EMP_NO, 1, 7), 14, '*') VAL
FROM EMPLOYEE;

7. LTRIM / RTRIM (문자열, 제거하려는 문자)

: 주어진 컬럼이나 문자열의 왼쪽/오른쪽에서 지정한 문자 혹은 문자열을 제거한 나머지를 반환하는 함수

 

1) 아래 RTRIM 결과값 : KH

SELECT 
	RTRIM('KH5782', '0123456789') 
FROM DUAL; 

 

8. TRIM (제거하려는 문자 FROM 문자열 | 컬럼)

: 주어진 컬럼이나 문자열의 앞/뒤에 지정한 문자를 모두 제거!

-- 양쪽에서 문자 제거
SELECT
	TRIM('D' FROM 'DDDDDDDDABCDDD')
FROM DUAL;
SELECT TRIM(BOTH 'D' FROM 'DDDDDDDDABCDDD') FROM DUAL;

-- 앞에서 문자 제거
SELECT TRIM(LEADING 'D' FROM 'DDDDDDDDABCDDD') FROM DUAL;

-- 뒤에서 문자 제거
SELECT TRIM(TRAILING 'D' FROM 'DDDDDDDDABCDDD') FROM DUAL;

 

9. CONCAT (문자열 | 컬럼 , 문자열 | 컬럼)

: 문자열 혹은 컬럼 2개를 입력받아 하나로 합친 후 리턴한다.

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

--아래와 같은 결과!
SELECT
	'가나다라' || 'ABC' AS VAL
FROM DUAL;

무조건 2개를 입력받아야 하기때문에 3개의 문자열 혹은 컬럼을 연결하기 위해 3개 이상을 입력하면 오류가 난다.

따라서 2개씩 CONCAT으로 합쳐줘야 한다.

-- 오류남
SELECT
    CONCAT('가나다라', 'ABC' 'ZE') VAL
FROM DUAL;

-- 오류 해결
SELECT
    CONCAT(CONCAT('가나다라', 'ABC'), 'ZE') VAL
FROM DUAL;

 

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

: REPLACE(컬럼 또는 문자열, '문자열1', '문자열2') -> 컬럼 또는 문자열에서 '문자열1'을 '문자열2'로 바꾼 후 리턴

1) 결과 -> 서울시 강남구 삼성동

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

 

 

 

숫자 함수 : ABS, MOD, ROUND, FLOOR, TRUNC, CEIL

1. ABS(숫자 | 숫자로 된 컬럼명)

: 절대값 구하는 함수

2. MOD(숫자 | 숫자로됨 컬럼명, 숫자 | 숫자로 된 컬럼명)

: 두 수를 나누어서 나머지를 구하는 함수 / 처음 인자를 뒤의 인자로 나누었을 때 나머지 반환

1) 음수인 값을 음수 또는 양수로 나누면 나머지도 음수로 출력된다.

SELECT 
    MOD(10,5) COL1    --0
    , MOD(10,3) COL2  --1
    , MOD(10,-3) COL3 --1
    , MOD(-10,3) COL4 -- -1
    , MOD(-10,-3) COL5 -- -1
FROM DUAL;

3. ROUND(숫자 | 숫자로 된 컬럼명, [위치])

: 반올림해서 리턴 / [위치]의 기본값은 0 -> 소수점이 없도록 일의자리까지 반올림해줌

SELECT ROUND(123.456, 2) FROM DUAL;   --123.46
SELECT ROUND(125.456, -1) FROM DUAL;  --130
SELECT ROUND(123.456, -2) FROM DUAL;  --100

 

4. FLOOR(숫자 | 숫자로 된 컬럼명)

: 내림처리하는 함수 / 숫자 혹은 컬럼의 소수점 자리수를 버리는 함수

SELECT FLOOR(123.678) FROM DUAL;  --123

 

5. TRUNC(숫자 | 숫자로 된 컬럼명, [위치])

: 내림처리(절삭)함수, 지정한 위치 이후의 소수점자리 수를 버리는 함수이다.

FLOOR와는 달리 버리는 위치를 지정할 수 있다.

SELECT TRUNC(123.456, 2) FROM DUAL;  --123.45
SELECT TRUNC(123.456, -1) FROM DUAL; --120

 

6. CEIL(숫자 | 숫자로 된 컬럼명)

: 올림처리 함수(소수점 기준으로 올림처리)

SELECT CEIL(123.456) FROM DUAL;  --124
SELECT CEIL(123.678) FROM DUAL;  --124

 

 

날짜함수 : SYSDATE, MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, EXTRACT

1. SYSDATE

: 시스템에 현재 날짜와 시간

SELECT SYSDATE FROM DUAL;  --21/06/22

 

2. MONTHS_BETWEEN(날짜, 날짜)

: 두 날짜 사이의 개월수

1) 입사후 6개월이 되는 날짜를 조회

SELECT
    EMP_NAME
    , HIRE_DATE
    , ADD_MONTHS(HIRE_DATE, 6) COL1
FROM EMPLOYEE;

2) 근무년수가 20년 이상인 직원 조회

SELECT
    *
FROM EMPLOYEE
WHERE MONTHS_BETWEEN(SYSDATE, HIRE_DATE) / 12 >= 20;

 

3. ADD_MONTHS(날짜, 숫자)

: 날짜에 숫자만큼의 개월수를 더해서 리턴

1) 입사일로부터 6개월 더한 날짜 조회

SELECT
	ADD_MONTHS(HIRE_DATE, 6) COL1
FROM EMPLOYEE;

2) 근무년수가 20년 이상인 직원 조회

EMPLOYEE테이블에 별칭으로 A를 줘서 사용했다!

SELECT
    A.*
    , ADD_MONTHS(HIRE_DATE, 240) VAL
FROM EMPLOYEE A
WHERE ADD_MONTHS(HIRE_DATE, 240) <= SYSDATE;

 

4. NEXT_DAY(기준날짜, 요일(문자|숫자) )

: 기준날짜에서 구하려는 요일에 가장 가까운 날짜 리턴

SELECT 
    SYSDATE
    , NEXT_DAY(SYSDATE, '금요일') AS NDAY
    --, NEXT_DAY(SYSDATE, '금') AS NDAY
    --, NEXT_DAY(SYSDATE, 6) AS NDAY
FROM DUAL;

 

5. LAST_DAY(날짜)

: 해당 날짜의 달에서 마지막 날짜를 구하여 리턴

SELECT SYSDATE, LAST_DAY(SYSDATE) LDAY FROM DUAL;

 

6. EXTRACT : 년월일 정보를 추출하여 리턴

EXTRACT(YEAR FROM 날짜) : 년도만 추출
EXTRACT(MONTH FROM 날짜) : 월만 추출
EXTRACT(DAY FROM 날짜) : 날짜만 추출

SELECT
    EMP_NAME 사원이름  --1
    , EXTRACT(YEAR FROM HIRE_DATE) 입사년  --2
    , EXTRACT(MONTH FROM HIRE_DATE) 입사월  --3
    , EXTRACT(DAY FROM HIRE_DATE) 입사일  --4
FROM EMPLOYEE
--ORDER BY 사원이름 DESC;
ORDER BY 2, 3 DESC;

 

 

형변환 함수

1. TO_CHAR( 숫자, [포멧] ) : 숫자형 데이터를 문자형 데이터로 변환

FORMAT	예시	설명
,	9,999	콤마 형식으로 변환
.	99.99	소수점 형식으로 변환
0	09999	왼쪽에 0을 삽입
$	$9999	$ 통화로 표시
L	L9999	LOCAL 통화로 표시(한국의 경우 \)
9		자릿수를 나타냄, 자릿수가 많지 않아도 0으로 채우지 않는다.
0		자릿수를 나타냄, 자릿수가 많지 않을 경우 0으로 채워준다.

1) 숫자 데이터 포맷 적용

SELECT TO_CHAR(1234) FROM DUAL;            --1234
SELECT TO_CHAR(1234, '99999') FROM DUAL;   --1234
SELECT TO_CHAR(1234, '00000') FROM DUAL;   --01234
SELECT TO_CHAR(1234, 'L99999') FROM DUAL;  --￦1234
SELECT TO_CHAR(1234, '$99,999') FROM DUAL; --$1,234
SELECT TO_CHAR(1234, '00,000') FROM DUAL;  --01,234
SELECT TO_CHAR(1234, '9.9EEEE') FROM DUAL; --1.2E+03
SELECT TO_CHAR(1234, '999') FROM DUAL;     -- ###

- 나타내려는 숫자 자릿수 > 포맷의 자리수 : ( #*포맷의 자리수) 으로 나타냄

SELECT TO_CHAR(1234, '999')  ==> ###

 

 

 2. TO_CHAR( 날짜, [포멧] ) : 날짜형 데이터를 문자형 데이터로 변환

1) 날짜 데이터 포맷 적용

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD PM HH24:MI:SS') FROM DUAL; --2021-06-23 오후 19:02:54
SELECT TO_CHAR(SYSDATE,'AM HH:MI:SS') FROM DUAL;      --오후 07:03:11
SELECT TO_CHAR(SYSDATE,'MON DY,YYYY') FROM DUAL;      --6월 화, 2021
SELECT TO_CHAR(SYSDATE,'YYYY-fmMM-DD DAY') FROM DUAL; --2021-6-22 화요일 
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD DAY') FROM DUAL;   --2021-06-22 화요일
SELECT TO_CHAR(TO_DATE('980630','RRMMDD'),'YYYY-fmMM-DD') FROM DUAL; -- 월앞에 0제거하고 싶을때 fm사용
--19998-6-30
SELECT TO_CHAR(SYSDATE,'YEAR,Q')||'분기' FROM DUAL;   --TWENTY TWENTY-ONE,2분기

FORMAT	예시	설명
PM / AM	(SYSDATE, 'AM HH:MI:SS')	오후 / 오전 표시
HH24:MI:SS	(SYSDATE, 'AM HH24:MI:SS') 오후 06:24:05	24시간 기준으로 나타냄
HH:MI:SS HHMISS	(SYSDATE, 'HH:MI:SS')	시간, 분, 초
YYYY-fmMM-YY YYYYMMDD	TO_CHAR(SYSDATE,'YYYY-fmMM-DD')	fm : 월 앞에 0제거 YYYY-MM-DD : 년-월-일
DAY	TO_CHAR(SYSDATE,'YYYY-fmMM-DD DAY')	요일 --> 화요일
YYYY	TO_CHAR(SYSDATE, 'YYYY')	년도 출력 2021
YY	TO_CHAR(SYSDATE, 'YY')	21
RRRR	TO_CHAR(SYSDATE, 'RRRR')	년도 출력 2021
RR	TO_CHAR(SYSDATE, 'RR')	21
MM MONTH MON RM	TO_CHAR(SYSDATE, 'MM')  TO_CHAR(SYSDATE, 'MONTH')	06 6월 6월 VI
DDD DD D	TO_CHAR(SYSDATE, '"1년기준 "DDD"일째"') TO_CHAR(SYSDATE, '"달기준 "DD"일째"')  TO_CHAR(SYSDATE, 'D')	1년기준 174일째 달기준 23일째 4

 

2) YY와 RR의 차이

-- YY: TO_DATE시 적용하면 2000년도 적용
-- 결과: 2098
SELECT 
    TO_CHAR(TO_DATE('980630','YYMMDD'), 'YYYY-MM-DD') VAL
FROM DUAL;

-- RR: 50년미만-2000적용 / 50년이상-1990년 적용
-- 결과 : 1998
SELECT 
    TO_CHAR(TO_DATE('980630','RRMMDD'), 'YYYY-MM-DD') VAL
FROM DUAL;

 

3. TO_DATE ( 문자형 데이터, [포멧] ) : 문자형 데이터를 날짜형 데이터로 변환하여 리턴

   TO_DATE ( 숫자, [포맷] ) : 숫자형 데이터를 날짜형 데이터로 변환하여 리턴

 

포맷은 TO_CHAR 날짜형을 문자형으로 변환하는 포멧과 동일하다.

--1. TO_CHAR & TO_DATE 비교
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD PM HH24:MI:SS') FROM DUAL;           --2021-06-23 오후 19:02:54
SELECT TO_DATE('041030 143000', 'YYYY-MM-DD PM HH24:MI:SS') FROM DUAL;  --041030
--TO_CHAR는 시간까지 나온다!!
--TO_DATE는 시간은 나오지 않는다!!

--2. TO_DATE : 문자형데이터, 숫자 -> 날짜형 데이터로 변환
SELECT TO_DATE(SYSDATE, 'YYYY-MM-DD PM HH24:MI:SS') FROM DUAL;  --날짜형 적으면 안됨

--3. 1) TO_DATE로 (문자->날짜형)
--   2) TO_CHAR로 (날짜형 -> 문자)
SELECT
    TO_CHAR((TO_DATE('041030 143000', 'YYMMDD HH24MISS')),'YY-MM-DD HH24:MI:SS') VAL
FROM DUAL;  
--04-10-30 14:30:00

1) TO_CHAR는 시간까지 나오지만, TO_DATE는 포맷을 지정해줘도 시간은 나오지 않는다!

 

 

 

 

출처 및 참고

0. KH정보교육원 강의자료

1. 사진 : https://rosebud90.tistory.com/entry/SQL-%EB%8B%A8%EC%9D%BC%ED%96%89-%ED%95%A8%EC%88%98