[SQL] 함수 - 단일행함수 (문자열함수/ 숫자관련/형변환함수/ 선택함수)

SQL 함수 (FUNCTION)

자바의 메소드와 비슷한 역할
전달된 값들을 읽은 후, 메서드에 맞는 계산결과를 반환
1) 단일행함수: n개의 값을 읽어서 n개의 값 리턴
2) 그룹함수: n개의 값을 읽어서 1개의 값 리턴
==> 단일행 함수와 그룹함수는 반환결과의 행수가 다르므로
일반적으로 하나의 쿼리문에 함께 사용할 수 없음!

단일행 함수

1. 문자열 관련 함수 (단일)

1) LENGTH / LENGTHB

- LENGTH (STR) : 전달된 STR의 글자수를 컬럼으로반환 (반환타입: NUMBER)
- LENGTHB (STR): 전달된 STR의 바이트 수를 컬럼으로 반환 (반환타입: NUMBER)
-> 숫자, 영문, 특문(!, ~) : 한글자당 1BYTE
-> 한글 ('ㄱ', 'ㅏ', '강'): 한글자당 3BYTE

SELECT LENGTH('ORACLE'),  LENGTHB('ORACLE') -- 알파벳은 하나당 1BYTE로 인식
  FROM DUAL;
SELECT LENGTH('오라클'), LENGTHB('오라클') -- 한글은 하나당 3BYTE 로 인식
  FROM DUAL;

SELECT EMAIL, LENGTH(EMAIL), LENGTHB(EMAIL), EMP_NAME, LENGTH(EMP_NAME),LENGTHB(EMP_NAME)
  FROM EMPLOYEE;

2) INSTR

- INSTR(STR) : 문자열로부터 특정 문자의 위치값을 반환 (반환타입: NUMBER)
INSTR(STR, STR내 '특정문자', POSITION, 순번 (몇번째 '특정문자'를 찾을건지 )
* POSITION : 시작위치
찾을 위치의 시작값, 순번은 생략가능 (생략시 STR 가장 앞부터 탐색)

SELECT INSTR('IWANTOGOHOMEEARLYTODAY', 'A')
  FROM DUAL; 
-- 찾을위치 생략시 기본적으로 가장 앞에서부터 첫번째 글자 탐색


SELECT INSTR('IWANTOGOHOMEEARLYTODAY', 'A', -1, 2)
FROM DUAL;  
------맨 뒤부터 'A'를 찾을건데, 2번째로 찾은 'A'가 문자열 앞에서부터 몇번째에 있는지


SELECT EMP_NAME AS "사원명", INSTR(EMAIL, '@') "@의 위치는?"
 FROM EMPLOYEE;

3) SUBSTR

- 문자열로부터 특정 문자열 추출하여 반환 (반환타입: CHARACTER)
- SUBSTR (STR, POSITION, LENGTH)
- STR: 내가 추출하고 싶은 문자열 혹은 문자타입컬럼
- POSITION: 시작위치
- LENGTH: 추출한 문자개수, 생략시 끝까지

SELECT SUBSTR('IWANTTOGOHOMEANDSLEEPEARLY', 6, 4) -- 위치 인덱스는 1부터 시작
  FROM DUAL;

SELECT SUBSTR('IWANTOGOHOMEEARLY', -5, 3) -- 뒤에서 5번째부터 3개
  FROM DUAL;

-------------주민번호 성별부분을 추출하여 남자인지 여자인지 체크-------
SELECT EMP_NAME, SUBSTR(EMP_NO, 8, 1) AS "성별"
  FROM EMPLOYEE;

-----남자사원들만 조회(사원명, 급여)------------------
SELECT EMP_NAME, SALARY
  FROM EMPLOYEE
 WHERE SUBSTR(EMP_NO,8,1) = '1'; 
 
----여자사원만 조회(사원명, 급여)------------------
SELECT EMP_NAME, SALARY
  FROM EMPLOYEE
 WHERE SUBSTR(EMP_NO,8,1) IN ('2', '4');

---이메일에서 아이디부분만 추출해서 조회---이름, 이메일 아이디
--EMAIL컬럼의 값에서 @ 앞까지 
-- EMAIL컬럼에서 @위치를 INSTR을 통해 반환받은 후 
--SUBSTR을 통해 1부터 @위치-1까지 반환받기
SELECT EMP_NAME, EMAIL, SUBSTR ( EMAIL,  1, INSTR(EMAIL, '@')-1)
  FROM EMPLOYEE;

4) LPAD /RPAD

LPAD: 문자열 왼쪽채우기
RPAD: 문자열 오른쪽 채우기

- LPAD /RPAD (STR, 최종 반환문자의 길이, 덧붙일 문자) : (CHARACTER 반환)
* 덧붙이는 문자는 생략 가능

SELECT RPAD('121212-2', 14, '*')  
  FROM DUAL;
 -- 121212-2******
 --기입한 문자열을 14자리로 채운 후 반환
 -- (14자리에 입력한 문자를 앞부터 기입 후, 남는 뒷쪽칸에는 *를 채워 반환)

--모든 직원의 주민번호 뒤 6자리를 마스킹하여 표현, 이름, 주민번호
SELECT EMP_NAME, RPAD(SUBSTR(EMP_NO, 1, 8), 14, '*')
  FROM EMPLOYEE;

5) LOWER / UPPER /INITCAP

- LOWER(STR) : 문자열을 전부 소문자로 변환
- UPPER(STR) : 문자열을 전부 대문자로 변환
- INITCAP(STR): 각 문자열 가장 앞문자만 대문자로 변환

SELECT LOWER ('WELCOME TO ORACLE')  -- welcome to oracle
FROM DUAL;

SELECT UPPER ('welCome to oRacle')  --WELCOME TO ORACLE
FROM DUAL;

SELECT INITCAP('welcome to oracle') -- Welcome to orcle
FROM DUAL;

6) CONCAT

- CONCAT(STR1, STR2) : 전달받은 문자열과 문자열을 하나로 연결하여 반환 (반환값 : CHARACTER)
- 연결연산자와( ||) 의 차이: 최대 2개 문자열만 연결 가능, 3개이상 불가!!

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

7) REPLACE

- REPLACE (STR, '찾을문자', '바꿀문자') : (반환값: CHARACTER)
- STR 내의 '찾을 문자'를 '바꿀문자'로 변환 후 반환

SELECT  REPLACE('오늘은 정말이지 피곤한 하루예요','피곤', '행복')
  FROM DUAL;  -- 오늘은 정말이지 행복한 하루예요

8) TRIM

- TRIM ( '지우고싶은 문자열' FROM '기존 문자열') : (반환값: CHARACTER)
- TRIM ( LEADING STR2 FROM STR1)
- TRIM ( TRAILING STR2 FROM STR1)
- TRIM ( BOTH STR2 FROM STR1)
- LEADING(앞쪽) / TRAILING(뒤쪽) / BOTH(앞뒤 : DEFAULT)
: 문자열 앞, 뒤, 앞뒤에 붙은 특정 문자를 제거한 나머지 문자열을 반환

SELECT TRIM('ㅋ'FROM'ㅋㅋㅋㅋㅋㅎ ㅏ피곤하다 ㅋㅋㅋ')
FROM DUAL;  -- 기본값 BOTH, 앞뒤값 제거, ㅎ ㅏ피곤하다

SELECT TRIM(LEADING'ㅋ'FROM'ㅋㅋㅋㅋㅋㅎ ㅏ피곤하다 ㅋㅋㅋ')
FROM DUAL;  -- 앞쪽 LEADING, 앞쪽값 제거, ㅎ ㅏ피곤하다 ㅋㅋㅋ
SELECT TRIM(TRAILING'ㅋ'FROM'ㅋㅋㅋㅋㅋㅎ ㅏ피곤하다 ㅋㅋㅋ')
FROM DUAL;  --뒤쪽 TRAILING, 뒤쪽값 제거, ㅋㅋㅋㅋㅋㅎ ㅏ피곤하다

2. 숫자 관련 함수

1) ABS (NUMBER) 절대값

SELECT ABS(-10) AS "절댓값"
  FROM DUAL;

2) MOD (나머지, 일반연산자로 못구함)
- MOD(NUM1, NUM2): 두 수를 나눈 나머지값 반환

SELECT MOD(10, 3)
  FROM DUAL;

3)ROUND

- ROUND (NUMBER, n) : 반올림,
- 소수점 아래 n번째 수에서 반올림 (생략가능 / DEFAULT : 0 )

SELECT ROUND(123.4567, 3)  -- 123.457
  FROM DUAL;

4) CEIL

- CEIL(NUMBER) : 올림
- 소수점 첫 번째 자리에서 무조건 올림, 정수반환

SELECT CEIL(123.1565)  -- 124
  FROM DUAL;

5) FLOOR

- FLOOR (NUMBER) : 올림
- 소수점 첫 번째 자리에서 무조건 버림, 정수반환

SELECT FLOOR (123.156)  ---123
  FROM DUAL;

-------------사원, 사원의 근무일수 반환--------
SELECT EMP_NAME, CONCAT(FLOOR(SYSDATE - HIRE_DATE), '일') AS "근무일수"  ---- SYSDATE는 소숫점이 더럽게 나오니 요러케 처리하면 됨
  FROM EMPLOYEE;

3. 형변환 함수

1 ) TO_CHAR

- 숫자형, 날짜형 데이터를 문자형타입으로 변환(반환값: CHARACTER)
- NUMBER / DATE => CHARACTER

[표현법] 
SELECT TO_CHAR (NUM/DATE , 변환할 형태지정)

SELECT TO_CHAR(SYSDATE) AS 문자열타입
  FROM DUAL;

SELECT TO_CHAR (YYYY-MM-DD) AS 문자열타입
  FROM DUAL;

SELECT TO_CHAR(1234, '00000') --- 앞내용을 뒷내용에 넣되 앞쪽빈칸에 0을 채우겠다
FROM DUAL;

SELECT TO_CHAR(1234,'99999')
FROM DUAL; -- 빈칸에 공백을 채워넣겠다, 그러면 9는 어떻게채워?

SELECT TO_CHAR(1234, 'L00000') -- L: 통화표시
FROM DUAL;

SELECT TO_CHAR(1234, 'L99,999') -- L: 통화표시 
 -- 숫자가 문자로 바뀌면서 자릿수를 넣어줄 수 있음
FROM DUAL;

SELECT EMP_NAME, TO_CHAR(SALARY, 'L999,999,999') "급여"
FROM EMPLOYEE; ---- 완전 편하다...

2) NULL 처리 함수 (NVL) **중요

해당 컬럼의 값이 NULL일 경우 , NULL 대신 지정한 값을 결과로 반환
1 > NVL(컬럼명, NULL을 대신할 지정값)
2 > NVL2 (컬럼명, NULL이 아닌 값 대신 넣을 내용, NULL일시 넣을 내용)
연산시, NULL이 있으면 NULL을 만나는 결과들은 다 NULL로 반환되는 현상을 막기 위해
NULL이 나올 수 있는 부분을 NVL로 감싸서 NULL이 아닌 지정한 값을 반환하도록 만든다

SELECT EMP_NAME, BONUS, (SALARY +(SALARY* NVL(BONUS, 0) ))*12 
  FROM EMPLOYEE;
"보너스 포함 연봉" 
----NULL로 인해 문제가 발생하는 BONUS를 다른 값으로 감싸줌
--- BONUS를 받지 못해 NULL이 들어있는 값들을 0으로 대체하여 연산
----완전 편하다..


SELECT EMP_NAME, NVL(DEPT_CODE, '부서없음') 
  FROM EMPLOYEE;
  
----NULL 대신 '부서없음' 이 반환됨


SELECT EMP_NAME, NVL2(DEPT_CODE, '부서있음', '부서없음')
  FROM EMPLOYEE;

4. 선택함수

1) DECODE

- 자바에서의 SWITCH문과 유사
- DECODE ( 비교대상(컬럼명, 산술연산, 함수), 조건값, 결과값)

--사원명이랑 주민등록번호로부터 성별자리 추출하여 1,2로 보여준 것 
SELECT EMP_NAME, SUBSTR(EMP_NO, 8, 1)
FROM EMPLOYEE;


SELECT EMP_NAME, DECODE ( SUBSTR(EMP_NO, 8, 1) , 1, '남', 2, '여') AS "성별"
FROM EMPLOYEE;

--직원들의 급여를 인상시켜 조회
--직급코드가 'J7'인 사원은 급여 10% 인상하여 조회
--직급코드가 'J8'인 사원은 급여 15% 인상하여 조회
--직급코드가 'J5'인 사원은 급여 20% 인상하여 조회
-- 사원명, 직급코드, 급여, 인상 후 급여
-- EMPLOYEE 테이블에서

SELECT EMP_NAME
           , JOB_CODE
           , SALARY
              AS "인상 전 급여"
           , DECODE ( JOB_CODE, 
                             'J5',SALARY*1.2 ,
                             'J8', SALARY*1.1 ,
                             'J7', SALARY*1.2 )
                        
                AS "인상 후 급여"

FROM EMPLOYEE;

2) CASE - WHEN - THEN 구문

-자바의 IF - IF ELSE

[표현법]
CASE WHEN 조건식 1 THEN 결과값 1
        WHEN 조건식 2 THEN 결과값 2
        ...
        ELSE 결과값
 END
*/
SELECT 
            EMP_NAME    
            , DECODE (
                            SUBSTR(EMP_NO, 8, 1), 1, '남', 2, '여') AS "성별"
FROM EMPLOYEE;
-----CASE WHEN THEN

SELECT EMP_NAME,
            CASE 
                   WHEN SUBSTR(EMP_NO, 8 , 1) = '1' THEN '남'
                   ELSE '여'
            END
FROM EMPLOYEE;

-----사원명, 급여, 급여등급

SELECT*
FROM EMPLOYEE;
-- 사원명, 급여, 급여등급 (초급, 중급, 고급)
-- SALARY값이 500만원 초과일 경우 '고급'
 -- SALARY값이 500만원 이하 350만원 초과일 경우'중급'
--  350만원 이 하일 경우 '초급'

SELECT EMP_NAME, SALARY,
            CASE 
                    WHEN SALARY > 5000000 THEN '고급'
                    WHEN SALARY > 3500000 AND SALARY <= 5000000 THEN'중급'
                    ELSE '초급'
             END 
FROM EMPLOYEE;

저작자표시 비영리 동일조건

'Database' 카테고리의 다른 글

[SQL] 집합연산자 (SET OPERATOR) (0)	2022.10.04
[SQL] 조인 (JOIN) (1)	2022.10.04
[SQL] 함수 - 그룹함수 (0)	2022.10.04
[SQL] DML - SELECT문 (0)	2022.10.04
[SQL] 명령어의 구분 (DML / DDL/ DCL/TCL) (0)	2022.10.04

🌱코딩새싹 코린이의 코딩일기🖥️

[SQL] 함수 - 단일행함수 (문자열함수/ 숫자관련/형변환함수/ 선택함수)

SQL 함수 (FUNCTION)

단일행 함수

'Database' 카테고리의 다른 글

티스토리툴바

« 2024/07 »
일	월	화	수	목	금	토
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31

[SQL] 함수 - 단일행함수 (문자열함수/ 숫자관련/형변환함수/ 선택함수)

SQL 함수 (FUNCTION)

단일행 함수

'Database' 카테고리의 다른 글

'Database' 관련글

티스토리툴바