오라클 데이터베이스(5) - 문자열 내장함수 / 숫자 내장 함수 / 날짜함수 / 형식변환함수 / NULL 관련 함수

문자열 내장함수

1. 문자 쪼개기 SUBSTR 

-- 첫번째를 기분으로 3개를 잘라주라
SELECT SUBSTR('HELLO', 1 ,3) FROM DUAL;

-- 세번째부터 잘라주라
SELECT SUBSTR('HELLO',3) FROM DUAL;

-- 바이트
SELECT SUBSTRB('HELLO', 3) FROM DUAL;

Q. MEMBER TABLE에 BIRTHDAY 열은 YYYY-MM-DD로 구성되어 있는데, MEMBER의 NAME열과 그사람의 출생 월을 추출하는 함수를 구해보자

-- 6번째 자리에서 2글자 뽑아내기
SELECT NAME, SUBSTR(BIRTHDAY, 6,2) FROM MEMBERS;

Q. 전화번호가 011로 시작하는 회원의 모든 정보 추출하기

SELECT*FROM MEMBERS WHERE SUBSTR(PHONE, 1, 3) = '011';

SELECT*FROM MEMBERS WHERE PHONE LIKE '011%';

Q. 회원 중 생일이 7, 8, 9월인 회원의 모든 정보 추출하기

SELECT*FROM MEMBERS WHERE SUBSTR(BIRTHDAY, 6, 2) IN (07, 08, 09);

Q. 전화번호를 등록하기 않은 회원 중에서 생년 월이 7, 8, 9일인 회원의 모든 정보를 출력하기

SELECT*FROM MEMBERS WHERE PHONE IS NULL & SUBSTR(BIRTHDAY, 6, 2) IN (07, 08, 09);

 

2. 문자열 덧셈 함수

SELECT CONCAT('홍, '길동') FROM DUAL;

SELECT 3||'4' FROM DUAL;

 

3. 빈 공백을 없애는 TRIM 함수

-- 왼쪽 공백 제거
SELECT LTRIM('    HELLO     ') FROM DUAL;

-- 오른쪽 공백 제거
SELECT RTRIM('    HELLO     ') FROM DUAL;

-- 양쪽 공백 제거
SELECT TRIM('    HELLO    ') FROM DUAL;

 

4. 문자열 소문자LOWER 또는 대문자UPPER로 변경하기

SELECT LOWER('NeWIEC') FROM DUAL;

SELECT UPPER('neWlec') FROM DUAL;

 

5. 문자열 대치 함수 REPLACE / TRANSLATE

-- 단어단위 REPLACE
SELECT REPLACE('WHERE WE ARE', 'WE', 'YOU') FROM DUAL;
> WE라는 단어를 YOU라는 단어로 바꿔준다

-- 철자 단위 TRANSLATE
SELECT REPLACE('WHERE WE ARE', 'WE','YOU) FROM DUAL;
> W는 Y, E는 O U는 의미없음

Q. 회원의 이름과 주소를 조회하시오(단, 주소는 빈칸이 없게 출력해야함)

SELECT NAME, REPLACE(ADDRESS, ' ','') FROM STUDENTS;

' '(공백) ->''(공백 없도록)

 

6. 패딩을 이용하여 공백 채우기 (바이트 기준) 

-- HELLO를 10글자로 할당하되, 공간이 남을 시 빈 곳은 왼쪽부터 0으로 채우기
SELECT LPAD('HELLO', 10, '0') FROM DUAL;
> 00000HELLO

-- 오른쪽부터 0으로 채우기
SELECT RPAD('HELLO', 10, '0') FROM DUAL;
> HELLO000000

 

7. 문자열 검색 함수INSTR (문자열, 검색문자열, 위치, 찾을 수)

-- TO의 위치를 찾아라
SELECT INSTR('ALL WE NEED TO IS JUST TO ...', 'TO') FROM DUAL;
> 13 (첫번째 나오는 TO의 자리수 출력해줌)

-- 두번째 나오는 TO의 위치를 찾아라
SELECT INSTR('ALL WE NEED TO IS JUST TO ...', 1, 2) FROM DUAL;

> 1번을 넘기고 2번째 나오는 TO를 찾아주라

Q. 회원의 전화번호에서 두 번째 대시(-) 문자가 존재하는 위치를 출력하라

SELECT INSTR(PHONE, '-', 1, 2) FROM MEMBERS;

8. 문자열 길이 출력하기 LENGTH

Q. 회원의 전화번호 컬럼에 포함된 문자'-'를 없앤 전화번호의 길이를 출력하라

SELECT LENGTH(REPLACE(PHONE, '-', '') FROM MEMBERS;

 

숫자열 내장함수

1. 절댓값 ABS(n)

SELECT ABS(-32) FROM DUAL;
> 32

 

2. 음수, 양수 SIGN(n)

SELECT SIGN(35), SIGN(-35), SIGN(0) FROM DUAL;
> 1, -1, 0

 

3. 숫자의 반올림 값을 알려주는 함수 ROUND(n, 반올림 자리수)

SELECT ROUND(34.456789), ROUND(34.56478) FROM DUAL;
> 34, 35

SELECT ROUND(12.3456789, 2), ROUND(12.3456789, 3) FROM DUAL;
> 12.35, 12.346

 

4. 숫자의 몫(TRUNC(n/m))과 나머지(MOD(n1, n2)) 값을 반환하는 함수 

SELECT TRUNC(17/5) 몫, MOD(17, 5) 나머지 FROM DUAL;

 

5. 숫자의 제곱(POWER)을 구하는 함수와 제곱근(SQRT)을 구하는 함수

SELECT POWER(5, 2), SQRT(25) FROM DUAL;
> 5의 제곱 = 25, 25의 제곱근 =5

 

 

날짜함수

1. 년월일(SYSDATE, CURRENT_DATE) / 년월일시분초(SYSTIMESTAMP, CURRENT_TIMESTAMP)

SELECT SYSDATE, CURRENT_DATE, SYSTIMESTAMP, CURRENT_TIMESTAMP FROM DUAL;

 

2. 한국식으로 세션 시간과 포맷 변경

ALTER SESSION SET TIME_ZONE = '-1:0'
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

기존 설정은 미국식이여서 2번을 하고 1번을 해주자

 

 

3. 날짜 추출 함수 EXTRACT

SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
> 2023

SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL;
> 07

SELECT EXTRACT(DAY FROM SYSDATE) FROM DAUL;
> 28

SELECT EXTRACT(MINUTE FROM SYSDATE) FROM DAUL;
> 25

Q. 가입 회원 중에 비수기(2, 3, 11, 12)월 달에 가입한 회원을 조회하시오

SELECT * FORM MEMBERS WHERE EXTRACT(MONTH FROM REGDATE) IN (02,03, 11, 12);

 

4. 날짜를 누적하기 ADD_MONTH(날짜, 정수)

SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL;
> 현재 달에 1달 더 추가 23.08.28

SELECT ADD_MONTHS(SYSDATE, -1) FROM DUAL;
> 현재 달에 1달 빼기 23.06.28

 

Q. 가입 회원 중 가입한지 6개월이 안 되는 회원을 조회하시오

SELECT * FROM MEMBERS WHERE ADD_MONTHS(SYSDATE, -6) < REGDATE;

 

5. 날짜의 (달) 차이를 알아내는 함수 MONTHS_BETWEEN(날짜, 날짜)

SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2013-12-25')) FROM DUAL;

몇 달 차이나는지 월로 보여줌

 

SELECT MONTHS_BETWEEN(SYSDATE, REGDATE) FROM MEMBERS;

결과가 이렇게 나옴 1달하고 좀더.. 이런 식으로 소수점 형식으로

 

Q. 가입 회원 중 가입한지 6개월이 안 되는 회원을 조회하시오

SELECT *FROM MEMBERS WHERE MONTHS_BETWEEN(SYSDATE, REGDATE) < 6;

 

 

형식 변환 함수

숫자 -> 문자 : TO_CHAR() / 문자 -> 날짜 : TO_DATE()

날짜 -> 문자 : TO_CHAR() / 문자-> 숫자 : TO_NUMMBER()

 

NUMBER형식을 문자열로 변환 TO_CHAR(NUMBER, 포맷 지정)

SELECT TO_CHAR(12345678, '99,999,999,999.99') FROM DUAL;
결과 : 12,345,678

근데 이렇게 하면 11자리의 공간이 생기게 된다 하지만 1-8까지는 8자리이기 때문에 3자리의 빈공백이 생기게 됨

TRIM 을 써주면 됨

--소숫점 표현하기
SELECT TRIM(TO_CHAR(1234567.34, '9,999,999,999.99') FROM DUAL;
결과 1,234,567.34

 

DATE 형식을 문자열로 변환 TO_CHAR(DATETIME)

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

포맷 형식 지정 (날짜 포맷은 더 있음)

 

문자열을 날짜 형식으로 변환하는 함수 TO_DATE(문자열, 날짜포맷)

SELECT TO DATE('1994-01-01','YYYY-MM-DD') FROM DUAL;

SELECT TO DATE('1994-01-01 12:23:03', 'YYYY-MM-DD HH:HI:SS') FROM DUAL;

 

문자열을 숫자열로 변환하는 함수 TO_NUMBER(문자열)

SELECT TO_NUMBER('1994') FROM DUAL;

 

 

NULL 관련 함수

반환 값이 NULL일 경우에 대체 값을 제공하는 NVL(반환값, 대체값) 함수

SELECT NVL(AGE, 0) FROM 
-- AGE값이 NULL일 경우 0으로 대체하겠다

AGE값이 NULL일 경우는 0으로 변환하여 나이를 10으로 나누고 그것의 몫을 구하는 식
(왜냐면 AGE값이 NULL인데 10으로 나누면 여전히 NULL이기 때문)
SELECT TRUNC(NVL(AGE,0)/10)*10 FROM MEMBERS;

반환값이 NULL이 아닌 경우에만 연산하도록 NVL2(반환값, 연산, 대체값)

AGE의 값이 NULL이면 0을, NULL이 아닌 경우만 /10으로 나누는 연산을 할 수 있도록
SELECT NVL2(AGE, TRUNC(AGE/10)*10,0) FROM MEMBERS;

물론 NVL을 쓴 경우와 NVL2를 쓴 경우 둘 다 결과는 같지만, NVL2의 경우 먼저 NULL인지 아닌지 분류를 한 뒤 연산을 하기 때문에 데이터 양이 많을 경우 연산 수행 수 자체가 적음 (이득이란 소리)

 

두 값이 같을 경우 NULL, 그렇지 않을 경우 첫번 째 값을 반환 NULLIF(값1, 값2)

SELECT NULLIF(AGE, 19) FROM MEMBERS;
-- AGE가 19인 경우 NULL, 그렇지 않은 경우 원래 AGE값 도출

 

조건에 따른 값 선택하기 DECODE(기준값, 비교값, 출력값, 비교값, 출력값...)

SELECT DECODE(SUBSTR(PTHONE,1,3), '011',
'SK', '016',
'KT', '기타') FROM MEMBERS;
-- 폰 앞자리 3글자를 뽑아내서 011이면 SK, 016이면 KT를 아니면 기타 출력

 

https://youtu.be/dE7z40fQbBM

이걸로 공부하는 중