백엔드/데이터베이스

SQL 함수(숫자형 함수, 문자형 함수)

study_yeon 2023. 7. 10. 13:49

2023.07.07 수업


● SQL 함수

- 수학 함수의 정의와 비슷
- 여러 자주 사용하는 기능을 한 함수이름으로 정의해 놓으면 필요할때 불러서 사용할 수 있다

 

▶ SQL 제공함수

 

▷ 숫자형 함수

- 입력되는 파라미터(매개변수)가 숫자 계산결과(반환값)이 숫자인 함수

 

1) 절대값 함수 ABS(n)
- 매개변수 n의 절대값을 반환

SELECT ABS(-199) FROM DUAL;   -- 결과 : -199


2) 천장함수 CEIL(n)
- 매개변수 n과 같거나 큰 최소정수 반환(올림 기능)

SELECT CEIL(10.5) FROM DUAL;   -- 결과 : 11


3) 바닥함수 FLOOR(n)
- 매개변수 n과 같거나 작은 최대정수 반환(버림 기능)

 SELECT FLOOR(10.5) FROM DUAL;   -- 결과 : 10


4) 지수승(=거듭제곱, exponentiation)함수 POWER(x , n)
- X ^ 3 = X * X * X, X의 3승
- 매개변수 n만큼 x(밑의 수)를 거듭해서 곱한다

SELECT POWER(3 , 5) FROM DUAL;   -- 결과 : 243



5) 밑을 e(2.71828183...)로 두는 지수승(=거듭제곱) EXP(n)
- e ^ 3 = e * e * e
- 매개변수 n만큼 e(밑의 수)를 거듭해서 곱한다

SELECT EXP(1) FROM DUAL;  -- 결과 : 2.71828183



6) 자연로그 LN(n)
- 매개변수 n의 자연로그 값을 반환 (n은 0보다 커야함)

SELECT LN(10) FROM DUAL;  -- 결과 : 2.30258509299405


7) LOG 값(지수승) 구하기 LOG(n2, n1) 
- n2를 밑으로하고 n1을 진수(결과값)
- LOG(10, 100)은 10 ^ 2 = 10 * 10 = 100이므로 LOG는 거듭제곱과 역함수 관계
- LOG는 밑(n1)을 몇번 곱하면 진수(결과값)이 되느냐의 지수승을 구하는 함수

SELECT LOG(10, 100) FROM DUAL;  -- 결과 : 2

-> LOG(10, 100) = 2 => 10 * 10 이므로 10(밑) 진수(100) 10을 두 번 곱함 (거듭제곱)

8) 나머지 구하는 함수 MOD(n2, n1)
- n2 / n1의 나머지 반환

SELECT MOD(11, 4) FROM DUAL;  -- 결과 : 3


9) 반올림 함수 ROUND(n, i)

* 소수점자리 양수 지정
- n의 값을 (i + 1)번째 소숫점 자리에서 반올림

- 소수점 자리수(i) + 1의 자리에서 반올림하여 결과는 소수점자리수(i)로 나타남

SELECT ROUND(1025.5487, 1) FROM DUAL; -- 결과 : 1025.5 
SELECT ROUND(1025.5487, 2) FROM DUAL; -- 결과 : 1025.55 
SELECT ROUND(1025.5487, 3) FROM DUAL; -- 결과 : 1025.549 
-- 소수점 자리수가 0인 경우
SELECT ROUND(1405.1543, 0) FROM DUAL;  -- 결과 : 1405

* 소수점자리 음수 지정

-- 소수점 자리수 음수지정(정수를 오른쪽부터 가르킴)
SELECT ROUND(1405.1543, -1) FROM DUAL;  -- 결과 : 1410
SELECT ROUND(1405.1543, -2) FROM DUAL;  -- 결과 : 1400
SELECT ROUND(1405.1543, -3) FROM DUAL;  -- 결과 : 1000
SELECT ROUND(1405.1543, -4) FROM DUAL;  -- 결과 : 0
SELECT ROUND(5405.1543, -4) FROM DUAL;  -- 결과 : 10000

- 정수부분의 오른쪽을 의미, 정수 첫째자리에서 반올림 하라
- 음수지정하고 최상위 자리(5미만)에서 반올림하면 결과 0
- 음수지정하고 최상위 자리(5이상)에서 반올림하면 결과 나옴

10) 싸인함수 SIGN(n)
- 값 n의 부호를 반환
- n이 양수이면 1, 음수이면 -1, 0이면 0 반환

 SELECT SIGN(-100) FROM DUAL;  -- 결과 : -1


11) 제곱근 함수 SQRT(n)
- 값 n의 제곱근 값을 반환

SELECT SQRT(4) FROM DUAL;  -- 결과 : 2


12) 버림함수 TRUNC(n1, n2)
- 소수점 이하 n2자리 이하 버림(절사)
- n2 생략시 0 

SELECT TRUNC(1025.5487, 1) FROM DUAL; -- 결과 : 1025.5
SELECT TRUNC(1025.5487, 2) FROM DUAL; -- 결과 : 1025.54
SELECT TRUNC(1025.5487, 3) FROM DUAL; -- 결과 : 1025.548


▷ 문자형 함수

- 데이터처리에서 가장 사용비율이 높음
- 문자형 데이터를 문자형 함수를 이용하여 문자열을 변형한 결과를 얻는다
- 문자형 함수는 입력값이 보통 문자형이고 결과도 대부분 문자형이다
- 일부 문자열의 개수를 반환하라 
- 검색할 문자열이 있는 위치를 반환하는 등의 숫자가 반환하기도 한다


1) 문자로 변환 CHR(n) 
- n은 숫자이며, n값에 해당하는 애스키코드(ASCII) 값을 돌려줌

SELECT CHR(65) FROM DUAL;  -- 결과 : A

* 애스키코드
65부터는 대문자 'A' 부터
97부터는 소문자 'a' 부터

-- 1. CHR(n) 
SELECT CHR(72) || CHR(105) || CHR(126) FROM DUAL;  -- 결과 : Hi~


2) 문자열 합치기 CONCAT(char1, char3) = char1 || char2

SELECT CONCAT('a', 'B') FROM DUAL; -- 결과 : aB


3) INITCAP(char)
- char문자열의 첫 번째 문자를 대문자로 변환

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


4) UPPER(char)
- char문자열을 모두 대문자로 변환

SELECT UPPER('hello') FROM DUAL; -- 결과 : HELLO


5) LOWER(char)
- char문자열을 모두 소문자로 변환

SELECT LOWER('HELLO') FROM DUAL; -- 결과 : hello


6) LPAD(str1, n, str2)
- str1(표시할 문자열), n(총 글자수), str2(채움 문자열)
- str1을 반환할때 str2를 (n-str1)개 만큼 왼쪽에 채워서 반환

SELECT LPAD('Hello', 10, '$') FROM DUAL; -- 결과 : $$$$$HELLO


7) RPAD(str1, n, str2)
- str1(표시할 문자열), n(총 글자수), str2(채움 문자열)
- str1을 반환할때 str2를 (n-str1)개 만큼 오른쪽에 채워서 반환

SELECT LPAD('Hello', 10, '$') FROM DUAL; -- 결과 : HELLO$$$$$


8) LTRIM(표시할 문자열, 제거할 문자)
- 표시할 문자열 중에서 왼쪽부터 제거할 문자를 제거하고 남은 문자열을 반환

SELECT LTRIM('##Hello##', '#') FROM DUAL; -- 결과 : HELLO##


9) RTRIM(표시할 문자열, 제거할 문자)
- 표시할 문자열 중에서 오른쪽부터 제거할 문자를 제거하고 남은 문자열을 반환

 SELECT RTRIM('##Hello##', '#') FROM DUAL; -- 결과 : ##HELLO


10) TRIM(표시할 문자열)
- 표시할 문자열 중에서 왼쪽과 오른쪽에서 공백를 제거하고 남은 문자열을 반환

SELECT LTRIM(' Hello  ') FROM DUAL; -- 결과 : HELLO


11) SUBSTR(원본문자열, n1, n2)
- 원본문자열에서 n1의 위치에서 시작하여 n2의 길이만큼 잘라낸 결과 문자열을 반환한다
- n1이 0인경우 n1 = 1 이 적용
- n1이 음수인 경우 검색방향이 오른쪽 끝에서 거꾸로 계산
- n2는 생략가능, 생략하면 n1부터 끝까지
- n2는 0이하 값이 입력되면 값이 없음(=NULL) 반환

-- 11. 부분문자열 가져오기 
-- 기본
SELECT SUBSTR('ABCDEFG', 1, 3) AS "부분문자열" FROM DUAL; -- 결과 : ABC

-- 0에서 시작해서 3개 가져오기 (0부터 시작하면 데이터베이스는 시작을 1로 본다)
SELECT SUBSTR('ABCDEFG', 0, 3) "0부터 시작해서 3글자 가져오기" FROM DUAL; -- 결과 : ABC

-- 시작위치만 지정하는 경우
SELECT SUBSTR('ABCDEFG', 2) "부분문자열" FROM DUAL; -- 결과 : BSDEFG

-- 시작위치를 음수(-2)로 지정하는 경우 : 오른쪽에서 시작
SELECT SUBSTR('ABCDEFG', -2, 1) "부분문자열" FROM DUAL; -- 결과 : F
SELECT SUBSTR('ABCDEFG', -2) "부분문자열" FROM DUAL; -- 결과 : FG
SELECT SUBSTR('ABCDEFG', -2, -1) "부분문자열" FROM DUAL; -- 결과 : NULL


12) 문자형 함수 중 숫자값을 반환하는 함수
* ASCII(문자) : 문자를 정의된 ASCII코드 값을 돌려줌

SELECT ASCII(CHR(65)) "문자 A의 ASCII코드 값" FROM DUAL; -- 결과 : 65
SELECT ASCII('A') "문자 A의 ASCII코드 값" FROM DUAL; -- 결과 : 65


* INSTR(char1, char2, n1, n2) 
- char1에서 char2를 찾아서 시작되는 위치 반환
- char1 : 원본문자열
- n1 : 처음 찾기 시작할 위치 
- n2 : 찾은 결과 위치를 몇 번째 위치의 결과값으로 반환할 것인가

SELECT INSTR('HELLO', 'L', 2, 1) FROM DUAL; -- 결과 : 3

 

-> 해석 : char1('HELLO') 문자열에서 char2('L')를 찾는데  'n1(2)' 두번째 문자부터 찾아 결과값이 'n2(1)' 첫번째 문자열으로 가져와라 

SELECT INSTR('HELLO', 'L', 2, 1) FROM DUAL; -- 결과 : 3
SELECT INSTR('HELLO', 'L', 3, 1) FROM DUAL; -- 결과 : 3
SELECT INSTR('HELLO', 'L', 3, 2) FROM DUAL; -- 결과 : 4
SELECT INSTR('HELLO', 'L', 4, 1) FROM DUAL; -- 결과 : 4
SELECT INSTR('HELLO', 'L', 4, 2) FROM DUAL; -- 결과 : 0

- SQL에서 값 지정하기(서브쿼리 활용)

-- 4번째에서 시작해서 첫번째 'L' 찾기
SELECT INSTR(
	'HELLO', 'L', INSTR('HELLO', 'L', 1, 1) + 1, 1)
	FROM DUAL; -- 결과 : 4
SELECT INSTR(
	'HELLO', 'L', INSTR('HELLO', 'L', 1, 2), 1)
	FROM DUAL; -- 결과 : 4
-- 대문자 변환    
SELECT INSTR(UPPER('OR!Or!Or!OR!Or!'), 'OR', 1, 4) FROM DUAL; -- 결과 : 10
SELECT INSTR(UPPER('OR!Or!Or!OR!Or!'), 'OR', -1, 4) FROM DUAL; -- 결과 : 10


* LENGTH(char)
- 문자열의 길이, 크기를 글자수로 돌려줌

SELECT LENGTH('HELLO') FROM DUAL; -- 결과 : 5