SQL - 단일 행 함수(문자/숫자/날짜/변환/조건)

 

🎯 SQL의 단일행 함수인 문자함수, 숫자함수, 날짜함수, 변환함수, 조건함수를 알아본다.

 

 

 

단일 행 함수

 

입력처리되는 행의 개수와 출력 처리되는 행의 개수가 동일하다.

14명 자료 있다면 함수가 14명에 다 적용된 결과를 보여준다.

 

 

 

문자함수

 

INITCAP 함수 : INITCAP('ORACLE SQL')    >>    Oracle Sql

각 단어의 첫 문자를 대문자로, 나머지는 소문자로 변경

 

 

LOWER 함수 : LOWER('ORACLE SQL')    >>    oracle sql

모든 문자열을 소문자로 변경.

 

 

UPPER 함수 : UPPER('Oracle Sql')    >>    ORACLE SQL

소문자를 대문자로 변경.

 

 

CONCAT 함수 : CONCAT('Oracle', 'Sql')    >>    OracleSql

두개의 문자열을 합성. || 연결연산자랑 같은 역할. 매개변수는 두개만 사용가능. 두개만 붙일 수 있다.

 

 

LPAD 함수 : LAPAD('MILLER', 10, '*')    >>    ****MILLER

글자를 총 n자리로 만들어주고 옆으로 밀어준 다음 왼쪽부터 새로운 문자열을 끼워넣는다. 주민번호에 많이 사용하는 형식. 왼쪽부터라서 LEFT PAD임. 삽입할 문자가 없을 경우 공백으로 채워진다.

 

 

RPAD 함수 : LAPAD('MILLER', 10, '*')    >>    MILLER****

오른쪽에 문자열을 끼워넣는 역할

 

 

SUBSTR 함수: SUBSTR( '12345', 3, 2)    >>    3, 4

문자중에서 특정부분만 뽑아줌

이 문자열에서 m 위치부터 n개의 문자를 뽑아줘.

몇년도에 입사했는지 년도 가져와서 구구절절 설명써보자.

 

 

주민번호를 보여주는 몇가지 방법

필요한 부분을 추출하고 남은 뒷부분에 연결연산자로 텍스트를 붙여준다.

 

SUBSTR 와 RPAD 함수 사용

(이 번호의 첫번째 자리부터 8개를 뽑아라),전체길이 14만들고, 숫자 오른쪽으로 밀고 남은자리 *로 채워라

 

SUBSTR과 REPLACE 함수

주민번호에서 9번째 자리부터 그 뒤 모두를 ******으로 바꿔준다.

시작 위치값만 지정하고 반환받을 문자열 개수를 지정하지 않으면 그 위치부터의 모든 문자열을 반환한다.

위치값이 음수면 거꾸로 뒤에서부터 몇번째인지 찾는다

 

 

LENGTH 함수 : LENGTH('ORACLE')    >>    6

문자열의 길이를 숫자값으로 리턴

이름의 길이가 긴 사람부터 내림차순 해주세요.

 

 

REPLACE 함수 : REPLACE( 'JACK and JUE', 'J', 'BL ')    >>     BLACK and BLUE

특정 문자열 바꿔주기

EMP 테이블에서 JOB에서 'A'를 '$'로 바꾸어 출력

 

 

INSTR 함수 : INSTR('MILLER', 'L', 1, 2)    >>    4

문자열이 포함되어있는지 조사해 문자열의 위치를 리턴. 지정한 문자열 발견되지 않으면 0을 리턴.

밀러라는 단어에서, L을 찾는데, 첫번째 자리부터 찾아, 근데 2번째 L이 어느자리에 있나 반환해라.

m과 n 은 옵션으로 생략가능. 생략하면 첫째자리부터 시작해서 첫번째로 검색되는 문자의 위치값을 반환하게 된다.

 

 

LTRIM 함수 : LTRIM('MILLER', 'M')    >>    ILLER

문자열의 첫 문자부터 확인해서 지정 문자가 나타나는 동안 해당 문자를 제거. 지정 문자를 생략하면 공백문자가 기본값. 왼쪽부터 시작해서 LEFT TRIM 이다.

 

 

RTRIM 함수 : RTRIM('MILLER', 'R')    >>    MILLE

문자열의 마지막 문자부터 확인해서 지정문자가 나타날 때까지 해당 문자를 제거한다.

TRIM 함수

문자열에서 왼쪽(leading) 오른쪽(trailing) 둘다(both) 제거. 이 세개가 생략되면 both 가 기본.

제거할 문자를 입력하지 않으면 기본적으로 공백이 제거됨.

LTRIM RTRIM 과는 다르게 TRIM 에서는 FROM 키워드를 사용해야 한다.

 

 

 

숫자함수

 

CEIL 함수 : CEIL(10.1)    >>    11

올림함수. 주어진 숫자보다 크거나 같은 최소 정수 리턴

 

 

FLOOR 함수 : FLOOR(10.23)    >>    10

소수점 밑자리 버리기. 주어진 숫자보다 작거나 같은 최대 정수 리턴.

 

 

MOD 함수 : MOD(10, 3)    >>    1

나머지를 반환하는 함수. 매개변수 2개, 주어진 숫자를 N으로 나누어 남은 값을 반환.

대표적으로 홀짝 구하기, 배수 구하기에 사용됨.

0으로 나눌경우 원래 값 그대로를 반환.

 

 

ROUND함수 : ROUND(123.456, 2)    >>    123.46

숫자를 소수점 N자리에서 반올림. 생략하면 기본값은 0(소수점 밀고 반올림)

N이 양수이면 소수자리, 음수이면 소수점을 기준으로 앞으로가서 정수자리를 반올림한다.

 

 

TRUNC 함수 : TRUNC(456.789, -1)    >>    450

소수점 기준으로 몇번째 자리를 버리는 함수. 음수이면 정수자리에서 버림.

SIGN 함수 : SIGN (100)    >>    1

주어진 숫자가 양수면 1반환, 음수면 -1 반환, 0=0인지 판단

월급이 1500보다 큰 사원만 출력하는 시퀄문을 부등호 연산 사용안하고 작성 가능.

월급에서 1500 마이너스 했을 때 양수면 1500보다 큰 값이기 때문에(아하!) where절 조건식에 sign 함수를 적용할 수 있다.

 

 

 

날짜함수

오라클 날짜에 대해 연산. MONTH_BETWEEN 제외하고는 DATE형을 리턴한다.

오라클 날짜데이터 기본 포맷 : RR/MM/DD

 

 

RR과 YY의 차이?

 

YY : 입력날짜를 오라클 서버의 현재 날짜 세기와 동일하게 처리.

95년도 자료 찾고 싶어서 YY=95를 입력해도 2095가 반환된다

RR : 오라클이 자동으로 반환년도를 결정. 정확도 UP!

 

 

SYSDATE 함수

오라클 시스템의 현재 날짜를 반환

SYSDATE+1은 날짜+1이 된다.

문제 ) EMP 테이블에서 현재까지 근무일 수가 몇주 몇일 인가를 출력. 단 근무 일수가 많은 사람 순으로 출력 — 소수점을 깔끔하게 잘 처리해주기

 

EMP 테이블에서 10번 부서 중 현재까지의 근무 월수를 계산하여 출력

 

 

ADD_MONTHS 함수

개월수를 더하고 뺄수있다. 결과는 날짜형.

EMP 테이블에서 10번 부서 중 입사 일자로부터 5개월이 지난 후 날짜 를 계산하여 출력

 

 

NEXT_DAY 함수

지정된 날짜를 기준으로 돌아오는 가장 가까운 요일에 해당하는 날짜를 반환.

요일 대신 숫자를 써도 되는데 일요일부터 1로 시작한다.

 

 

LAST_DAY 함수

월의 마지막 날짜 계산.

 

 

ROUND 함수

날짜를 가장 가까운 년도 또는 월로 반올림 할 수 있다

 

 

TRUNC 함수

날짜를 가장 가까운 년도 또는 월로 버림할 수 있다.

월 버림 하면 1일로 세팅, 연 버림하면 1월 1일로 세팅.

 

 

 

변환함수

데이터타입을 다른 데이터타입으로 변환시키는 함수

 

 

데이터 타입의 변환 방법

 

묵시적 형 변환

우리가 안바꿔줘도 오라클에서 자동적으로 바꿔주는게 있는데 권장안함

오라클이 문자로 입력된 7900을 자동으로 숫자로 형변환해서 찾아준다.

 

명시적 형 변환

내가 필요한 경우에 명시적으로 함수이용.

 

 

TO_CHAR : TO_CHAR(HIREDATE, 'YYYY')    >>    2018

문자로 형변환. 데이터 형 변환시 추가로 출력 형식을 지정할 수 있다. 출력형식은 반드시 따옴표 안에

 

 

TO_CHAR(사용할 데이터, 원하는 출력 형태)

 

입사날짜가 9월인 사원들만 보기

 

형식은 작은따옴표로 시작, 작은 따옴표가 중복되지 않게 그 안에는 큰 따옴표를 사용해줌.

 

EMP 테이블에서 부서 20중 급여 앞에 $를 삽입하고 3자리마다 ,를 출력

 

 

TO_NUMBER 함수 : TO_NUMBER('1234')    >>    1234

숫자형태의 문자열을 숫자로 변환

 

 

TO_DATE 함수 : TO_DATE('20200719', 'YYYYMMDD')    >>    2020/07/19

날짜를 나타내는 문자열을 명시된 날짜로 변환

 

 

 

일반함수(조건함수)

 

CASE 함수

조건이 반드시 일치하지 않아도 범위 및 비교가 가능한 경우 사용하는 함수

여러 조건에 대해 선택적으로 시퀄문을 실행시킬 수 있는 함수

-- 조건이 일치하는 경우 사용되는 기본 문법(동등조건)

SELECT 컬럼,
	CASE 컬럼 WHEN 비교값1 THEN 결과값1
		  WHEN 비교값2 THEN 결과값2
		  ELSE 결과값n
	END
FROM 테이블;

SELECT 컬럼부분에 CASE를 쓴다.

지정한 컬럼에서 WHEN ~ THEN 조건을 계속 검사하고

여기에 해당 안될경우 ELSE를 출력하고 END

END 뒤에 컬럼 별칭을 쓸 수 있는데, "별칭" 뒤에 따로 쉼표가 붙지 않는 거 주의하기.

직업별 보너스 차등지급

 

샐러리 기준에 따라 등급 부여하기

 

-- 조건이 반드시 일치하지 않는 경우 사용되는 기본 문법(부등조건)

SELECT 컬럼,
	CASE WHEN 조건1 THEN 결과값1
  	     WHEN 조건2 THEN 결과값2
	     ELSE 결과값n
	END
FROM 테이블;

부등비교 연산자(=말고 다른 나머지 비교연산자들, BETWEEN, IN, 논리연산자 등)를 사용할 경우 WHEN 안에 컬럼명이 조건으로 사용되기 때문에 CASE 다음에 컬럼을 또 적지 않는다.

 

 

DECODE함수

조건이 반드시 일치해야 되는 경우 사용되는 함수.

SELECT 컬럼,
	DECODE(컬럼, 비교값1, 결과값1,
		     비교값2, 결과값2,
	  	     ...
		     기본결과값)
FROM 테이블;

 

-- 월급에 따라 보너스 차등 지급하기위한 시퀄문 만들기

-- 월급 2400이면 월급의 30%, 1700이면 월급의 20%를 보너스로 지급받고

-- 위 조건을 만족하지 않은 나머지 사원은 월급의 100%를 보너스로 지급받는다. 👇👇

-- DECODE 버전

SELECT ENAME, SAL,
	DECODE(SAL, 2400, SAL*0.3,
	            1700, SAL*0.2,
		  SAL) 보너스
FROM EMP
ORDER BY 2 DESC;

-- CASE 버전

SELECT ENAME, SAL,
	CASE SAL WHEN 2400 THEN SAL*0.3
		 WHEN 1700 THEN SAL*0.2
	         ELSE SAL
	         END 보너스
FROM EMP
ORDER BY 2 DESC;

 

 

 

예제 풀어보기

 

1. 사원 테이블에서 영업사원과 비영업사원을 구별하여 출력해보기

먼저 나의 오답

 

지난번 말했지만 동등연산자를 이용해 null 값인 데이터를 찾고싶으면 결과가 안나온다.

NULL은 0과 달라서, 아예 값을 가지지 않았기 때문에 연산이 불가능하다!

따라서 DECODE가 아닌 CASE 함수를 사용해야 한다. 

👇👇

정답!

 

 

2. 부서 테이블에서 부서 위치를 미국의 동부, 중부, 서부로 구분하여 출력해보기

굳

 

 

😜 NEXT : SQL 다중 행 함수-그룹함수 공부하기