단일행 함수(single row function)

- nvl(인자1, 인자2): 값이 있으면 그대로 나오고 null이면 인자2 값을 집어넣음

SELECT first_name 이름, nvl(commission_pct, 0) 인센티브
FROM employees;
--nvl(인자1, 인자2): 인자1과 인자2의 타입이 같아야됨
SELECT first_name 이름, nvl(to_char(commission_pct), '없어!') 인센티브
FROM employees;
--to_char() -> 문자열로 바꿔줌

 

- LOWER, UPPER, INITCAP

SELECT first_name 이름, last_name 성, salary*12 연봉
FROM employees
WHERE LOWER(last_name)='king';

SELECT first_name 이름, last_name 성, salary*12 연봉
FROM employees
WHERE UPPER(last_name)='KING';
--INITCAP: 맨 앞글자만 대문자로 바꾸기
SELECT INITCAP('i will live my life for sQL') FROM dual;

 

- substr(salary,1,2): 1번째부터 2글자(인덱스 1부터 시작)

SELECT first_name 이름, substr(salary,1,2) 급여
FROM employees;

 

- lpad(값,전체칸수,채우는문자): 문자 채우기

SELECT lpad('ondal', 10, '#') FROM dual;
SELECT first_name 이름, lpad(salary,10, '*') 급여
FROM employees;

 

- ltrim: 문자지우기

SELECT LTRIM('#####ondal','#') FROM dual;

--처음에 a가 없어서 지워지지 않음 -> ondal
SELECT LTRIM('ondal','a') FROM dual;

--각각 적용됨 -> null 다지워짐
SELECT LTRIM('apple','aple') FROM dual;

--우선순위 없음 -> le
SELECT LTRIM('apple','pa') FROM dual;
--'apple' 에서 먼저 'a'를 꺼내와서 우측에 비교후 있으면 삭제, 그리고 'p'꺼내옴 이렇게 반복됨

- char, varchar2 비교하기

CREATE TABLE aatable (
    NAME CHAR(5)
);
CREATE TABLE bbtable (
    NAME VARCHAR2(5)
);
INSERT INTO aatable VALUES('babo');
INSERT INTO bbtable VALUES('babo');
--이 두개는 길이가 다름 char는 빈칸이 있음
SELECT * FROM aatable;
SELECT * FROM bbtable; 
--char랑 varchar2는 서로 비교하지 않는것이 좋음, 비교할떈 rtrim으로 빈칸 없애기
SELECT aatable.NAME, bbtable.NAME
FROM aatable, bbtable
WHERE RTRIM(aatable.name)=bbtable.name;

 

- translate, replace: 값 변환하기

--translate(값, 인자1, 인자2): 값의 인자1을 인자2로 바꿈
--apple -> abble
SELECT TRANSLATE('apple', 'pl', 'bk') FROM dual; --abbke
--문자 단위 변환(각각변환) p -> b, l -> k

--replace: 문자열 단위 변환
SELECT REPLACE('apple', 'pl', 'bk') FROM dual; --apbke

SELECT TRANSLATE('apple','pa','yy') FROM dual;
SELECT REPLACE('apple','pa','yy') FROM dual;

--날짜형식에선 replace 많이씀, 문자단위는 이상하게 바뀌기때문에? 문자열 단위로 바꿔주는게 좋음
SELECT TRANSLATE(sysdate,'/','-') FROM dual;
SELECT REPLACE(sysdate,'/','-') FROM dual;

--자리수에 맞춰서 모든 영문자는 w로 바뀌고 숫자는 0으로 바뀜
SELECT TRANSLATE('apple1234','abcdefghijklmnopqrstuvwxyz0123456789','wwwwwwwwwwwwwwwwwwwwwwwww0000000000') FROM dual;
-- w 26개, 0 9개

--이메일에 숫자가 포함되어있는 사원 구하기
SELECT first_name 이름, email 메일
FROM employees
WHERE TRANSLATE(email, '0123456789', '0') != email;

 

- length: 길이 구하기

Q) 사원 이름이 7글자 이하인 직원 구하기

SELECT first_name 이름, salary 급여, hire_date 입사일
FROM employees
WHERE LENGTH(first_name) <= 7;

 

- round(): 반올림

SELECT ROUND(23.576) FROM dual; --24
SELECT ROUND(23.576,1) FROM dual; --23.6 , 소수이하 첫째자리까지
SELECT ROUND(23.576,2) FROM dual; --23.58 , 소수이하 둘째자리까지
SELECT ROUND(23.576,-1) FROM dual; --20, 일의 자리 반올림

- trunc(): 절사, 소수자리 버림

SELECT TRUNC(36.754) FROM dual;
SELECT TRUNC(36.754, 1) FROM dual;
SELECT TRUNC(36.754, 2) FROM dual;
SELECT TRUNC(36.754, -1) FROM dual; --30

- 날짜 반올림

--날짜 반올림 월은 mon, 년은 year, 요일은 day, 일은 dd
--날짜 형식은 to_date 해줘야됨, 6월 31일 기준
SELECT ROUND(to_date('20/07/01'), 'year') FROM dual;
SELECT ROUND(to_date('20/06/30'), 'year') FROM dual;
--2월도 15일이 기준, 무조건 15일 기준
SELECT ROUND(to_date('20/02/15'), 'mon') FROM dual;
SELECT ROUND(to_date('20/02/16'), 'mon') FROM dual;
--day는 주 기준(가까운 일요일), 수요일(시간까지 계산)
SELECT ROUND(to_date('20/12/09'), 'day') FROM dual;
SELECT ROUND(sysdate, 'day') FROM dual;
--시간을 기준으로 반올림
SELECT ROUND(to_date('20/12/09'), 'dd') FROM dual;
SELECT ROUND(sysdate, 'dd') FROM dual;

 

- MOD(): 나머지 연산자(%연산자)

SELECT MOD(76, 10) FROM dual; --7

 

- add_months(): 월 더하기

--오늘부터 6개월 후 (월 더하기): add_months()
SELECT sysdate 오늘, add_months(sysdate,6) FROM dual;

- MONTHS_BETWEEN(나중날짜, 과거날짜): 몇개월지났는지

SELECT ROUND(MONTHS_BETWEEN(to_date('20/04/13'), to_date('18/08/27'))) FROM dual;
SELECT ROUND(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) FROM EMPLOYEES;
--몇년 근무했냐
SELECT ROUND(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)||'년' 근무년수 FROM EMPLOYEES;

- next_day(): 돌아오는 요일 구하기

SELECT next_day(sysdate, '목') FROM dual;

- last_day(): 이번 달의 마지막 날

SELECT last_day(sysdate) FROM dual;

SELECT first_name 이름, hire_date 입사일, salary 급여, ROUND(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)||'년' 근무년수
FROM employees
WHERE ROUND(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)>=15
ORDER BY 근무년수 DESC, 급여 DESC;

 

- concat(): 결합연산자(||)의 메소드형식

SELECT concat(first_name, '의 성은') "이름에 대한 성은?", last_name 성
FROM employees;

 

- to_char()

숫자 -> 문자

SELECT '$'||to_char(8000) FROM dual;
--틀에 넣는 방법, 틀에 넣어서 문자열로 바꿈
SELECT to_char(1234567, '$999,999,999') FROM dual; --$1,234,567
SELECT to_char(12345.67, '$999,999,999.99') FROM dual; --$12,345.67
SELECT to_char(12.34567, '$999,999,999.99') FROM dual; --$12.35 반올림됨
SELECT to_char(12345.67, '$999,999,999.990000') FROM dual; -- $12,345.670000 나머지 0으로 채움

SELECT to_char(123.50, '$999,999,999.99') FROM dual;
select to_char(1234567,'$999999.9999999') from dual; --오른쪽의 자리수는 왼쪽보다 같거나 많아야됨

SELECT to_char(123.50, '$000,000,000.99') FROM dual; -- 0으로 채우면 빈 자리수 0으로 채움

날짜 -> 문자

SELECT to_char(sysdate, 'yyyy-mm-dd') FROM dual;
ALTER SESSION SET NLS_DATE_FORMAT = 'YY/MM/DD hh24:mi:ss'; --이번 접속동안에만 시간 표현을 이렇게 바꿈
SELECT sysdate FROM dual;

--to_char 사용해 05년도 입사한 사람 찾기
SELECT first_name 이름, hire_date 입사일
FROM employees
WHERE to_char(hire_date, 'yy')='05';

 

- to_date

--to_date       이문자열을      이런형식으로 적어놓은거다
SELECT to_date('2020 12 10', 'yyyy mm dd') FROM dual;

 

- to_number()

SELECT to_number('12345') FROM dual;