SQL 함수

임시테이블 DUAL

SELECT 'HELLO' FROM DUAL;

DUAL은 임시테이블로 값을 확인하는 용도로 쓴다. 새로운 테이블이나 데이터를 만들지 않아도 값을 확인하기에 편리하다. 이 경우에는 HELLO를 확인할 수 있다.

 

 

SQL 함수

문자열 대소문자 변경하는 함수

INITCAP : 단어별 첫글자가 알파벳일 때 대문자로 변경

SELECT INITCAP('hello hello') FROM DUAL; --결과값 : Hello Hello

INITCAP을 이용하여 영어 단어 첫글자를 대문자로 변경했다. Hello Hello가 출력된 테이블을 확인할 수 있다.

 

 

LOWER : 알파벳을 전부 소문자로 변경

UPPER : 알파벳을 전부 대문자로 변경

SELECT LOWER('Hello World') FROM DUAL; --결과값 : hello world
SELECT UPPER('Hello World') FROM DUAL; --결과값 : HELLO WORLD

LOWER을 한 결과는 hello world, UPPER을 한 경우는 HELLO WORLD가 된 것을 확인할 수 있다.

 

 

글자 개수 및 바이트 수를 구하는 함수

LENGTH : 글자 개수 구하는 함수

SELECT LENGTH('HELLO'), LENGTH('안녕하세요') FROM DUAL; --결과값 : 5,5

HELLO, 안녕하세요 모두 5글자이다.

 

 

LENGTHB : 글자 바이트 수 구하는 함수

SELECT LENGTHB('HELLO'), LENGTHB('안녕하세요') FROM DUAL; --결과값 : 5, 15

HELLO는 5바이트, 안녕하세요는 15바이트를 확인할 수 있다. 한글은 한글자에 3바이트이다.

 

 

문자열 합치기

CONCAT : 두 문자열을 하나의 문자열로 합치기

SELECT CONCAT('HELLO','WORLD') FROM DUAL; --결과값 : HELLOWORLD

HELLOWORLD로 합쳐진 결과를 확인할 수 있다.

 

 

EX. 전화번호 데이터 '010' '1111' '1234' 를 하나의 문자열로 합치기

SELECT CONCAT(CONCAT('010','1111'),'1234') FROM DUAL; --결과값 : 01011111234

CONCAT은 두개의 문자열을 합치는 함수임을 주의해야 한다. CONCAT(010,1111,1234) 이런식으로 문자를 3개이상 한번에 이용할 수 없다. 하나씩 합쳐주는 방식으로 여러 문자열을 합칠 수 있다.

 

 

연산자 ||

SELECT '010'||'1111'||'1234' FROM DUAL; --결과값 : 01011111234

함수 외에도 연산자 || 를 이용하면 문자열을 한번에 붙일 수 있다.

이 연산자 ||는 문자열 외에 숫자나 다른 데이터도 합칠 수 있다.

 

SELECT MAJOR_NO || MAJOR_NAME FROM MAJOR;

두 컬럼의 데이터를 한번에 모아서 확인할 수 있다.

 

 

문자열 추출

SUBSTR : 문자열 부분 추출 (문자 기준으로 추출)

 

SELECT SUBSTR('123456789',5,4) FROM DUAL; --결과값 : 5678

'문자열'에서 ,몇번째 문자부터, 몇글자를 추출할 것인지 입력하고 실행하면 된다. 여기서는 5번째인 5부터 네글자인 5678이 출력된다.

 

SELECT SUBSTR('안녕하세요',2,1) FROM DUAL; --결과값 : 녕

글자단위기 때문에 한글도 '안녕하세요'의 두번째 글자 1개 '녕'이 확인된다.

 

 

EX. 주민등록번호 '888888-1231231' 에서 뒷자리 중 첫글자를 추출

SELECT SUBSTR('888888-1231231',8,1); --결과값 :1

8번째 나오는 글자부터 1개를 추출

 

 

EX. 주민번호 뒷자리의 첫자리를 제외하고 *으로 마스킹 처리

SELECT SUBSTR('888888-1231231',1,8)||'******' FROM DUAL;

첫번째 글자부터 뒷자리의 첫글자까지 추출한뒤 || 으로 '******'문자열과 이어주면 된다.

 

 

SELECT SUBSTRB('안녕하세요',1,3) FROM DUAL; --결과값 : 안

SUBSTRB는 바이트단위로 문자를 추출할 수 있다. 알파벳을 추출할 때는 유용하지만 한글을 추출할 때는 한글 한글자가 3바이트임을 주의해야 한다. 첫번째 바이트부터 3개의 바이트를 추출했기 때문에 '안'이 출력된다. 

3바이트 단위로 추출할 수 있고, 시작바이트도 1,4,7... 으로 3바이트 간격임을 주의해야 함수 이용이 가능하다.

 

 

 

INSTR 문자열 검색

SELECT INSTR('ABCDEFG','CD') FROM DUAL; --결과값 : 3

'ABCDEFG'에서 'CD'를 찾는다. 문자열중에 'CD'가 3번째글자에서 시작되기 때문에 위치값 3을 확인할 수 있다.

 

SELECT INSTR('ABCDEFG','AC') FROM DUAL; --결과값 : 0

'AC'는 해당 문자열에서 찾을 수 없기 때문에 위치값이 존재하지 않는다. 0으로 출력된다.

 

 

EX. HELLO WORLD에 공백이 있는지 체크

SELECT INSTR('HELLO WORLD',' ') FROM DUAL; --결과값 : 6

6번째글자 자리에 공백이 있기때문에 6으로 결과값이 뜬다. 만약 공백이 없다면 0으로 출력된다.

 

CHECK(INSTR(NAME,' ') = 0) 으로 이름에 공백이 들어가지 않도록 제약조건을 설정할 수있다.

 

 

문자열 바꾸기

REPLACE

SELECT REPLACE('ABCD','B','F') FROM DUAL; --결과값 : AFCD

'ABCD'문자열중에 'B'를 'F'로 바꾸어 주었다. AFCD가 결과값이 된다.

 

 

EX. 학생 테이블에서 이름 컬럼에 공백이 있는 학생의 이름을 공백을 지워서 업데이트

UPDATE STUDENT SET NAME = REPLACE(NAME, ' ', '') WHERE INSTR(NAME,' ') != 0 ;

컬럼 내 데이터를 수정하기 위해 UPDATE, SET을 이용했다. REPLACE 함수(), ISTR() 내에 처리할 문자열을 넣어주는 첫번째 자리에는 문자열이 직접올 수 있지만, 위와 같이 컬럼이 올 수도 있다.

 

 

 

PAD : 문자열의 자리수 맞추기 - RPAD, LPAD

원하는 문자열 개수만큼 남은 부분에 지정한 문자열로 채워주는 함수

SELECT RPAD('888888-1',14,'*') FROM DUAL; --결과값 : 888888-1******

원하는 글자수에 맞게끔 남는 자리수를 오른쪽에 채운다. 888888-1****** 이 된다.

 

SELECT LPAD('888888-1',14,'*') FROM DUAL; --결과값 : ******888888-1

원하는 글자수에 맞게끔 남는 자리수를 왼쪽에 채운다. ******888888-1 이 된다.

 

SELECT RPAD('ABC'10,'1234') FROM DUAL; --결과값 : ABC1234123

1234를 패턴처럼 남는 자리수만큼 찍어낸다.

 

RTRIM, LTRIM : 지정한 문자열을 제거

SELECT LTRIM('ABABABA','A') FROM DUAL; --결과값 : BABABA

 A가 양쪽에 있지만 LTRIM으로 왼쪽의 A만 제거되었다.

 

SELECT LTRIM('AAABBBCCCDDDAAA','A') FROM DUAL; --결과값 : BBBCCCDDDAAA

 왼쪽에서부터 A가 아닌 문자가 나올 때 까지의 연속된 A가 모두 제거되었다.

 

SELECT RTRIM('AAABBBCCCDDD','A') FROM DUAL; --결과값 : AAABBBCCCDDD

오른쪽 AAA만 제거 되었다.

 

TRIM : 좌우 공백을 제거

SELECT TRIM('       A A B       ') FROM DUAL; --결과값 : A A B

LENGTH(TRIM('       A A B       ')) 으로 글자수를 확인하면 5가 확인된다. 공백이 잘 제거되었음을 알 수 있다.

 

 

 

수학함수

 

ROUND : 원하는 자리수에서 반올림

SELECT ROUND(123.456,-2) FROM DUAL; -- 결과값 : 100
SELECT ROUND(123.456,-1) FROM DUAL; -- 결과값 : 120
SELECT ROUND(123.456,0) FROM DUAL; -- 결과값 : 123
SELECT ROUND(123.456,1) FROM DUAL; -- 결과값 : 123.5
SELECT ROUND(123.456,2) FROM DUAL; -- 결과값 : 123.46

 

자리수를 판단하기 위해 여러 명령을 실행했다. 

자리수	-3	-2	-1		0	1	2
값	1	2	3	.	4	5	6

소수점 첫번째 자리수가 0이고, 이처럼 자리수가 표현된다. 

 

ROUND(123.456,-2)는 십의자리(-2)에서 반올림,

ROUND(123.456,0)은 소수점 첫번째자리(0)에서 반올림,

ROUND(123.456,2)은 소수점 셋째자리(2)에서 반올림 한것이다.

 

 

TRUNC : 원하는 자리수에서 데이터 자름, 소수점 잘라낼 때 사용

SELECT TRUNC(123.456,-2) FROM DUAL; -- 결과값 : 100
SELECT TRUNC(123.456,-1) FROM DUAL; -- 결과값 : 120
SELECT TRUNC(123.456,0) FROM DUAL; -- 결과값 : 123
SELECT TRUNC(123.456,1) FROM DUAL; -- 결과값 : 123.4
SELECT TRUNC(123.456,2) FROM DUAL; -- 결과값 : 123.45

 

 

CEIL 올림 , FLOOR 내림

SELECT CEIL(123.456), FLOOR(123.456) FROM DUAL; -- 결과값 : 124, 123

CEIL, FLOOR는 소수점을 모두 없애고 올림과 내림의 차이만 있다.

 

 

MOD 나머지를 구하는 함수

SELECT MOD(7,3) FROM DUAL; --결과값 : 1

7나누기 3의 나머지인 1을 확인할 수 있다.

 

 

POWER(N,M) : N의 M승

SELECT POWER(2,10) FROM DUAL; --결과값 : 1024

 

TO_NUMBER('문자열') : 문자열을 숫자로 바꿔주는 함수

SELECT TO_NUMBER('123') FROM DUAL;

문자열 123이 숫자로 변환된다. 문자열에는 숫자만 들어가야 한다.

 

SELECT '123' + 456 FROM DUAL; --결과값 : 579

'123'은 TO_NUMBER으로 변환해주지 않았다. 하지만 결과는 123과 456을 더한 숫자값이 나온다. 데이터베이스에서는 문자열이라도 타입을 적절하게 자동으로 바꿔주기 때문이다.  

 

 

 

RANK : 순위

RANK() OVER (정렬 기준)

 

SELECT S.*, RANK() OVER (ORDER BY SCORE DESC) FROM STUDENT S;

ORDER BY 기준으로 정렬한 다음 RANK 순위를 매겨준다. 

 

 

 

 

날짜

SYSDATE : 오늘 날짜

SELECT SYSDATE FROM DUAL;

SYSDATE는 오늘 날짜를 확인해주는 함수이다. 기본 형식 22/05/13 으로 나타난다. 

 

 

TO_DATE('문자열') : 문자열을 날짜로 바꿔주는 함수

SELECT TO_DATE('22/05/31') FROM DUAL; --결과값 : 22/05/31

 

 

날짜 형식 바꾸기 

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

ALTER SESSION SET NLS_DATE_FORMAT 을 이용해서 오라클에서 지정된 현재 날짜 시간의 출력 포맷을 변경하였다.

다시 SYSDATE를 출력하면 2022-05-13 10:00:00 의 형식으로 나타난다.

 

 

MONTHS_BETWEEN 

SELECT ABS(MONTHS_BETWEEN
       (SYSDATE,'22/12/31')) FROM DUAL; --결과값 : 7.5619....

MONTHS_BETWEEN을 사용해서 지정날짜까지의 남은 월 계산을 할 수 있다.

오늘날짜부터 22/12/31 까지 남은 개월 수를 계산했다. 앞의 날짜에서 뒤의 날짜를 빼주는 것으로 -7.5619... 값이 나오기 때문에 항상 절대값이 나오도록 하는 ABS 함수도 추가하였다.

 

 

ADD_MONTHS

SELECT ADD_MONTHS(SYSDATE,2) FROM DUAL; --결과값 : 22/07/13

지정 날짜로부터 몇개월후의 날짜 /오늘날짜(SYSDATE)로부터 2개월 후의 날짜가 출력된다. 

 

 

NEXT_DAY

주어진 날짜 기준으로 원하는 요일이 돌아오는 날짜

SELECT NEXT_DAY(SYSDATE, '화') FROM DUAL; --결과값 : 22-05-17

이번주 화요일은 지났기 때문에 다음주 화요일인 날짜가 나온다. 

 

 

LAST_DAY

주어진 날짜 기준으로 날짜가 속한 달의 마지막 날

SELECT LAST_DAY(SYSDATE) FROM DUAL; --결과값 : 22/05/31

오늘날짜가 속한 5월의 마지막 날이 나온다.

 

 

내일 날짜 출력

SELECT SYSDATE +1 FROM DUAL;

날짜는 정수로 관리하기 때문에 하루는 1이다. +1을 하면 내일 날짜가 된다.

 

 

EX. 연말까지 남은 D-DAY 계산

SELECT TO_DATE('22/12/31') - SYSDATE FROM DUAL;

 

TO_CHAR(데이터,'형식') : 원하는 형식의 문자열로 변환

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL; --결과값 : 2022-05-13
SELECT TO_CHAR(SYSDATE,'MON MONTH DY DAY') FROM DUAL;  --결과값 5월 5월 금 금요일

 

NVL(NULL,'문자열') : NULL 대신 문자열이 나오도록 대체

SELECT NVL(NULL,'널입니다') FROM DUAL;

 

DECODE : 하나 이상의 동등(=)문 조건과 조작

DECODE(컬럼, 조건1, TRUE 결과1, FALSE 결과1, 조건2, TRUE 결과2, FALSE 결과2, 조건3, 결과3..........) 

NVL함수, DECODE 함수는 오라클에서만 존재하는 함수이니 주의해야 한다.

 

 

 

 

 

그룹함수 : SUM, AVG, MAX, MIN, COUNT, STDDEV(표준편차), VARIANCE(분산)

그룹함수는 함수명(컬럼명) 으로 사용하면 그룹을 묶은 기준으로 해당 컬럼을 가지고 계산한다.

그룹함수명(계산하고싶은 컬럼명) FROM 테이블명 GROUP BY 그룹 기준 컬럼

 

2022.05.20 - [DBMS/Oracle] - 그룹화(GROUP BY) , 그룹 함수(SUM, AVG, MAX, MIN, COUNT, STDDEV, VARIANCE)

그룹화(GROUP BY) , 그룹 함수(SUM, AVG, MAX, MIN, COUNT, STDDEV, VARIANCE)