(SQL 문법) SQL 함수 (단일행 함수)

단일행 함수

문자함수 (UPPER, LOWER, LENGTH, LPAD, RPAD, SUBSTR, TRIM)

-- 단일행 함수 -- 
-- 문자 함수
대문자로 출력 UPPER('값'), 소문자로 출력 LOWER('값')
문자 길이 출력 LENGTH('값'), 출력 문자 범위 SUBSTR('값',n번째부터,n글자)
LPAD('값',몇 글자,'*') :총 몇글자까지 공백을 왼쪽부터 '*'로 채운다. 
RPAD('값',몇 글자,'*') :총 몇글자까지 공백을 오른쪽부터 '*'로 채운다. 
TRIM('        값     ') : 공백제거

SELECT UPPER('abc') , LOWER('AAddedC'), LENGTH('abc'),
      '-'||TRIM(' abc  ')||'-',
      LPAD('abc', 7, '0'),  RPAD('abc', 7, '0'),
      SUBSTR('abcde', 1, 2),SUBSTR('abcde', 3, 2),
      SUBSTR('abcde', 2   ),  -- 2번째부터 다가져와
      SUBSTR('abcde', -1),    --맨뒤에서부터 다가져와
      substr('abcde', -3, 3)  --맨뒤에서부터 3글자만 가져와
FROM DUAL; 


SELECT ename, UPPER(ename) , LOWER(ename), LENGTH(ename),
      '-'||TRIM(ename)||'-',
      LPAD(ename, 7, '0'),  RPAD(ename, 7, '0'),
      SUBSTR(ename, 1, 2),SUBSTR(ename, 3, 2),
      SUBSTR(ename, 2   ),  -- 2번째부터 다가져와
      SUBSTR(ename, -1),    --맨뒤에서부터 다가져와
      SUBSTR(ename, -3, 3)  --맨뒤에서부터 3글자만 가져와
FROM emp; 


퀴즈)))
-- ename을 다음과 같은 형태로 출력
SMITH --> S**TH
WARD  --> W**D
MARTIN --> M**TIN
...

답안들)))
--김문식님이 모두에게 11:38 AM
SELECT SUBSTR(ENAME, 1,1)||LPAD('*',2,'*')||SUBSTR(ENAME, 3) ENAME FROM EMP;
--박희진님이 모두에게 11:38 AM
SELECT ENAME,SUBSTR(ENAME,1,1)||'*'||'*'||SUBSTR(ENAME, 4)
FROM EMP;
--이혜린님이 모두에게 11:38 AM
select substr(ename,1,1)||'**'||substr(ename,3)
FROM EMP;
--김효선님이 모두에게 11:38 AM
SELECT SUBSTR(ENAME,1,1) || '**'|| SUBSTR(ENAME,4) FROM EMP;
--최상현님이 모두에게 11:38 AM
select rpad(substr(ename,1,1),3,'*')||substr(ename,4) from emp;
--나라 신님이 모두에게 11:39 AM
select? replace (ename, SUBSTR (ename, 2,2), '**')
FROM EMP;

 

반올림/버림 함수 (TRUNC, ROUND, CEIL)

-- 한달 20일 기준: 일당, 반올림
round('값',자리수) - 반올림
trunc('값',자리수) - 버림
ceil(값) - 반올림하여 정수로 출력

-- 하루8시간 근무 기준 : 시급 소수점 1자리까지 나타내고 버림, 시급 소수점 1자리까지 나타내고 올림
SELECT SAL, SAL/20, ROUND(SAL/20,0) 
      , sal/20/8, trunc(sal/20/8, 1), ceil(sal/20/8 )
from emp;

 

타입 변환 함수 (TO_DATE, TO_CHAR, TO_NUMBER)

타입 변환 함수
TO_DATE(), TO_CHAR(), TO_NUMBER()

-- 형변환 == 타입변환 == 캐스팅
-- 날짜 타입 변환 3 
SELECT SYSDATE, 
       TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') ->시/분/초까지 출력
FROM DUAL;

-- 날짜 타입은 가감산 가능
SELECT HIREDATE, HIREDATE+1 ,
       -- to_char(hiredate, 'yyyy-mm-dd') + 1  버전마당 상이 현 버전에선 에러
from emp;

-- 글자로 날짜가 들어올 경우 to_date를 사용해 날짜 타입으로 캐스팅
SELECT TO_DATE('2021-01-01','yyyy-mm-dd')+1  FROM DUAL;

SELECT SYSDATE , HIREDATE , TRUNC(SYSDATE-HIREDATE,0), 
     trunc((SYSDATE-HIREDATE)/365,0)
from emp;

-- 입사일이 1982년도 이후 입사자
select *
FROM EMP
where hiredate > '1981-12-31';

select *
FROM EMP
WHERE HIREDATE > TO_DATE('1981-12-31','yyyy-mm-dd');

SELECT TO_DATE(SYSDATE, 'yyyy-mm-dd') FROM DUAL;      -- 현 버전에서는 구동
SELECT TO_CHAR('2021-01-01', 'yyyy-mm-dd') FROM DUAL; -- 현 버전에서는 에러
SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd') FROM DUAL;      -- **정석
SELECT TO_DATE('2021-01-01', 'yyyy-mm-dd') FROM DUAL; -- **정석

--1. 글자로 되있는 날짜라면 to_date를 사용해 날짜 타입으로 캐스팅
--2. 날짜 타입이면 연산,조건비교가 수월

-- 암묵적 형변환이 일어남 -- 글자123에  가감산이 가능
SELECT '123'+1 FROM DUAL;
SELECT TO_NUMBER('123') +1 FROM DUAL;


-- 함수중첩
CONCAT() = ||와 같은 기능

SELECT ENAME||'_kr'||'_123' FROM EMP;
SELECT CONCAT(ENAME,'_kr') FROM EMP;
SELECT CONCAT(ENAME,'_kr')||'_123' FROM EMP;
SELECT CONCAT(CONCAT(ENAME,'_kr'),'_123') FROM EMP;

--nvl *******NULL값 처리하는 함수 (중요)
select comm, comm+100, nvl(comm, 0),  nvl(comm, 0)+100 from emp;

조건문 (CASE, DECODE)

CASE 조건문 CASE WHEN THEN

SELECT DEPTNO, 
       CASE DEPTNO WHEN 10 THEN '십'
                   WHEN 20 THEN '이십'
                   WHEN 30 THEN '삼십'
                   ELSE '기타'
       END as kr
       -- CASE '값' WHEN 조건 THEN '조건에 해당하면 출력하는 값'
FROM DEPT;
SELECT SAL, 
        CASE       WHEN (SAL>=1000 and sal<2000) THEN '1'
                   WHEN (SAL>=2000 and sal<3000) THEN '2'
                   WHEN (SAL>=3000 and sal<4000) THEN '3'
                   ELSE '5'
        END as kr
FROM emp;

SELECT SAL, 
        CASE       WHEN (SAL BETWEEN 1000 AND 1999) THEN '1'
                   WHEN (SAL BETWEEN 2000 AND 2999) THEN '2'
                   WHEN (sal between 3000 and 3999) THEN '3'
                   ELSE '5'
        END as kr
FROM emp;


DECODE 조건문
DECODE(값, 조건1, 조건1해당하면출력, 조건2, 조건2해당하면출력,...,else값)
SELECT DEPTNO ,
       DECODE(DEPTNO, 10 , '십'
                    , 20 , '이십'
                    , 30, '삼십'
                    , '기타'
              ) as kr
from emp;