재밌고 어려운 IT를 이해해보자~!
Section 05. 다양한 SQL 함수 사용하기 본문
01. 문자열 함수
문자열과 문자열 연결하기: CONCAT 함수
문자열과 문자열은 연결 연산자 +를 사용해 연결할 수 있다.
오라클은 || 사용 !
SELECT DCODE || ':' || DNAME FROM DEPT2;
CONCAT 사용
SELECT CONCAT(DCODE,DNAME) FROM DEPT2;
오라클 에서는 CONCAT 함수는 2개의 문자열만 합칠 수 있어서
3개 이상의 문자열을 합칠 때 불편한 점이 있다. 그래서 보통 ||(파이프)를 많이 사용한다.
문자열과 숫자 또는 날짜 연결하기: CAST, CONVERT 함수
SELECT CAST(DCODE AS NUMBER(06)) FROM DEPT2;
VARCHAR였던 DCODE를 NUMBER로 캐스팅
오라클 CONVERT는 좀 다른 것 같다...!
- 인수 char은 변환되는 값이다. CHAR, VARCHAR2, NVARCHAR, NVARCHAR2, CLOB, NCLOB 데이터 중
하나가 가능하다고 한다.
- 인수 dest_char_set은 char이 변환되는 문자 세트의 이름이다.
- 인수 source_char_set은 char이 데이터베이스에 저장되는 문자 세트의 이름이다. 기본값은 데이터베이스 문자세트이다.
결과와 소스 문자 세트 인수는 문자(literal) 또는 문자 세트의 이름을 포함한 열을 지정할수 있다.
SQL> SELECT CONVERT(TITLE, 'KO16MSWIN949', 'AL16UTF16') FROM TB_DEER_CONTENT;
SQL> SELECT CONVERT(TITLE, 'KO16MSWIN949', 'AL32UTF8') FROM TB_DEER_CONTENT;
SQL> SELECT CONVERT(TITLE, 'KO16MSWIN949', 'UTF8') FROM TB_DEER_CONTENT;
SQL> SELECT CONVERT(TITLE, 'KO16MSWIN949', 'UTF8') FROM TB_DEER_CONTENT;
SQL> SELECT CONVERT(TITLE, 'KO16KSC5601', 'UTF8') FROM TB_DEER_CONTENT;
SQL> SELECT CONVERT(TITLE, 'UTF8') FROM TB_DEER_CONTENT;
KO16MSWIN949 : 조합형 한글 - 완성형을 포함하여 11172자의 한글을 표현함(한글:2Byte)
KO16KSC5601 : 완성형 한글 - 일반적으로 많이 사용되며 2350자의 한글, 4888자의 한자, 히라카나, 카타카나, 영문 및 각종 기호를 포함하고 있음(한글:2Byte)
US7ASCII : US 7비트 ASCII 문자 세트
WE8ISO8859P1 : ISO 8859-1 서유럽 8비트 문자 집합
EE8MSWIN1250 : Microsoft Windows 동유럽 코드 페이지 1250
WE8MSWIN1252 : Microsoft Windows 서유럽 코드 페이지 1252
WE8EBCDIC1047 : IBM 서유럽 EBCDIC 코드 페이지 1047
ZHT16MSWIN950 : Microsoft Windows 중국어 코드 페이지 950
WE8DEC : 서유럽 8-bit character set
F7DEC : DEC 프랑스-7bit character set
WE8EBCDIC500 : IBM 서유럽 EBCDIC Code Page 500
UTF8 : Unicode 3.0 범용 문자 집합 CESU-8 인코딩(한글바이트:3Byte)
AL16UTF16 : Unicode의 CES 중 하나. national characterset에서만 선택 가능. 모든 글자를 2byte or 4byte로 표현
AL32UTF8 : Unicode 5.0 범용 문자 집합 UTF-8 인코딩 형식(한글바이트:3Byte)
NULL과 문자열 연결하기: ISNULL, COALESCE 함수
만약 문자열과 연결하려는 값이 NULL이면 결과는 NULL로 반환된다.
따라서 테이블에 NULL이 있다면 문자열 또는 숫자로 자료형을 반환해야 한다.
NULL과 문자열 연결하기: ISNULL, COALESCE 함수
Oracle용 정리!
NVL
NULL이면 다른 값으로 변경하는 함수NVL(K, 0)은 K 컬럼이 NULL이면 0으로 바꿈
NVL2
NVL 함수와 DECODE 함수가 하나로 합쳐진 함수
NVL2(K, 1, -1)은 K컬럼이 NULL이 아니면 1을 NULL이면 -1을 반환
NULLIF
두개의 값이 같으면 NULL을 같지 않으면 첫번째 값을 반환하는 함수
NULLIF(exp1,exp2)은 exp1과 exp2가 같으면 NULL을 같지 않으면 exp1을 반환
COALESCE
NULL이 아닌 최초의 인자 값을 반환하는 함수
COALESCE(exp1,exp2,exp3,...)은 exp1부터 그 뒤로 차례대로 NULL인지 확인, 만약 exp2가 최초의 NULL이면 exp2를 반환
SELECT NVL(POSITION, ' ') FROM EMP2;
SELECT COALESCE(POSITION, NAME) FROM EMP2;
포지션에서 과장뒤는 쭉 NULL이라 이름을 반환
문자열을 소문자나 대문자로 변경하기: LOWER, UPPER, INTICAP 함수
SELECT INITCAP(ID) FROM PROFESSOR;
LOWER : 전체 소문자 변경.
UPPER : 전체 대문자 변경.
INTICAP : 첫문자만 대문자로 변경.
문자열 공백 제거하기: LTRIM, RTRIM, TRIM 함수
TRIM("문자열") : 모든 공백 제거.
LTRIM("문자열", "옵션") :옵션문자열 제거.
RTRIM("문자열", "옵션")
문자열 길이 반환하기: LEN 함수
LENGTH("문자열") : 문자열 길이 반환.
특정 문자까지 문자열 길이 반환하기: CHARINDEX 함수
INSTR ( [문자열], [찾을 문자 값], [찾기를 시작할 위치(1,-1)], [찾은 결과의 순번(1...n)] )
SELECT INSTR('Oracle Database 12c Release', 'as', 1, 2) AS result1 FROM dual
두번째 as 시작 위치 반환
지정한 길이만큼 문자열 반환하기: LEFT, RIGHT 함수
Oracle의 내장함수에는 LEFT()와 RIGHT()가 존재하지 않는다.
따라서 SUBSTR() 함수로 해당 기능을 구현해야 한다.
(1) 특정 자리부터 끝까지
SELECT SUBSTR('Hello, World!', 7)
>> World!
(2) 특정 자리부터 n글자
SELECT SUBSTR('Hello, World!', 1, 4)
>> Hell
(3) 우측부터 추출하고 싶을 때 ( Right 기능 )
SELECT SUBSTR('Hello, World!', -4)
지정한 범위의 문자열 반환하기: SUBSTRING 함수
위와 동일.
특정 문자를 다른 문자로 변경하기 : REPLACE 함수
REPLACE("칼럼명 or 문자열", "찾을문자", "치환문자")
SELECT REPLACE(REPLACE('Oracle Database', 'Oracle', 'Ora'), 'Database', 'DB')
FROM dual
문자 반복하기 : REPLICATE 함수
LPAD , RPAD는 오라클에서 사용.
SELECT LPAD(데이터, 고정길이, 문자) FROM DUAL
--// 고정길이 안에 데이터를 출력하고 남는 공간을 문자로 채운다
--// LPAD는 왼쪽을 채우고 RPAD는 오른쪽을 채웁니다
SELECT LPAD('A', 4, 'B') AS TEST_LPAD FROM DUAL
--// 결과: BBBA
SELECT RPAD('A', 4, 'B') AS TEST_RPAD FROM DUAL
--// 결과: ABBB
공백 문자 반복하기 : SPACE 함수
위와 동일.
문자열 역순으로 표시하기 : REVERSE 함수
REVERSE
- 형식 : REVERSE( "문자열" )
- 예시 : SELECT REVERSE( 'ABCDE' ) FROM DUAL
- 결과 : EDCBA
숫자를 문자열로 변환하기 : STR 함수
오라클 에선 TO_CHAR 사용.
TO_CHAR는 숫자 타입이나 날짜 타입의 데이터나 컬럼을 원본 데이터를 바꾸지 않고 문자 타입으로 형변환하여 출력해 주는 함수이다.
TO_CHAR (날짜또는숫자, [형식])
02. 날짜 함수
서버의 현재 날짜, 시간 반환하기: GETDATE, SYSDATETIME 함수
오라클은 SYSDATE 사용.
SELECT SYSDATE FROM DUAL;
서버의 현재 UTC날짜, 시간 반환하기: GETUTCDATE, SYSUTCDATETIME 함수
다음 예제는 지정한 datetime으로 부터 UTC를 추출
SELECT SYS_EXTRACT_UTC(TIMESTAMP '2000-03-28 11:30:00.00 -08:00')
FROM DUAL
날짜 더하기: DATEADD 함수
오라클은 ADDMONTH, -1,+1 사용
날짜 차이 구하기 : DATEDIFF 함수
오라클에서 날짜 및 시간의 차이를 계산하기 위해서는 수치를 계산하듯 종료일자에서 시작일자를 빼주면 된다. MSSQL에서는 DATEDIFF 함수를 사용하지만 오라클에서는 별도의 함수는 없다.
날짜 차이 : 종료일자(YYYY-MM-DD) - 시작일자(YYYY-MM-DD)
시간 차이 : (종료일시(YYYY-MM-DD HH:MI:SS) - 시작일시(YYYY-MM-DD HH:MI:SS)) * 24
분 차이 : (종료일시(YYYY-MM-DD HH:MI:SS) - 시작일시(YYYY-MM-DD HH:MI:SS)) * 24 * 60
초 차이 : (종료일시(YYYY-MM-DD HH:MI:SS) - 시작일시(YYYY-MM-DD HH:MI:SS)) * 24 * 60 * 60
SELECT TO_DATE('2021-05-08', 'YYYY-MM-DD') - TO_DATE('2021-05-01', 'YYYY-MM-DD')
FROM dual
=> 7일
날짜에서 일, 월, 연도 가져오기 : DAY, MONTH, YEAR 함수
오라클은 extract 사용.
select systimestamp,
extract (year from systimestamp) as year,
extract (month from systimestamp) as month,
extract (day from systimestamp) as day,
extract (hour from systimestamp) as hour,
extract (minute from systimestamp) as minute,
extract (second from systimestamp) as second
from dual
날짜 자료형 또는 형태 변환하기 : CONVERT 함수
오라클은 TO_CHAR() 사용
위에 확인하기.
03. 집계 함수
집계 함수는 where 절에 사용 불가★
COUNT(expr) - expr의 전체 개수 반환
지정 컬럼에 NULL 값이 있을 경우 건수로 세지 않는다.
expr은 컬럼을 포함한 표현식, 보통 * 사용
매개변수 ' * '는 COUNT 함수에서만 사용
MAX(expr) - expr의 최대값 반환
MIN ( expr ) - expr의 최솟값 반환
SUM ( expr ) - expr의 합계 반환
AVG ( expr ) - expr의 평균값 반환
VARIANCE ( expr ) - expr의 분산 반환
STDDEV ( expr ) - expr의 표준편차 반환
ROLLUP, CUBE, GROUPING SETS
[출처]
[Oracle]Convert 함수 https://blog.naver.com/PostView.nhn?
https://sgpassion.tistory.com/472 [Experience !!:티스토리]
'SQL 입문' 카테고리의 다른 글
| Section 19. 파일 입출력 (0) | 2024.01.02 |
|---|---|
| Section 04. 테이블을 서로 통합하는 조인 알아보기 (0) | 2023.12.24 |
| Section 03. SQL 시작하기 (1) | 2023.12.23 |
| Section 02. 나만의 SQL 실습 환경 만들기 (0) | 2023.12.21 |
| Section 01. 데이터베이스와 SQL의 기초 (0) | 2023.12.20 |
