2024.08.07. 단일행 함수 - 문자, 숫자

* 함수란? 

- 인수를 받아들여서 정해진 조작을 한 후 반드시 하나의 결과값을 반환함.
- SQL 함수 종류 - 단일행 함수 : 행당 조작해서 하나의 결과값을 반환함.
                           - 다중행 함수(=그룹함수) : 행그룹을 조작해서 하나의 결과값을 반환함.

 

* 문자함수

- ascii(문자) : 문자의 아스키코드값을 반환함.
- char(숫자) : 숫자(아스키코드값)의 문자를 반환함.

select ascii('A'), ascii('a'), char(65), char(97) from dual;
-- 단순한 함수결과나 계산을 출력해야하는 경우 접근할 테이블이 없을경우 dbms에서 제공해주는 테이블이 바로 dual이다.
-- char함수는 결과에 BLOB라고 뜨는데 오류가 아님.
-- SQL DEVELOPER에서 BLOB의 결과값을 보려면 해당칸 우클릭후 - open value in viewer - text 로 확인가능.

- length(문자열) : 문자열의 byte값을 반환함.
- bit_length(문자열) : 문자열의 bit 값을 반환함.
- char_length(문자열): 문자열의 문자 개수를 반환함.

select length('abc'), bit_length('abc'), char_length('abc') from dual;
select length('홍길동'), bit_length('홍길동'), char_length('홍길동') from dual;
-- 한글은 한글자가 3byte를 차지함. 
-- 게시판에 글자제한할때 사용함.ex. /1000자

- concat(문자열1, 문자열2, 문자열3, ....., 문자열n) : n개의 문자열을 연결함.
- concat_ws(구분자, 문자열1, 문자열2, ..., 문자열n) : 구분자와 함께 문자열을 연결함

  연결하고 싶은 값이 많다고 생각하면 '-'를 엄청 많이 적어야하는데 그때 사용하는 것이 concat_ws이다.

select employee_id, concat(first_name, ' ', last_name) as fullname, salary from employees;

select employee_id, concat(first_name, '-', last_name, '-', job_id) from employees;
-- (==)
select employee_id,concat_ws('-', first_name, last_name, job_id) from employees;

- instr(문자열, 특정문자)인스트링 : 문자열에서 특정문자의 첫번째 위치값을 반환함.

select last_name, instr(last_name, 'a') as "a의 첫번째 위치" from employees;
-- 찾는 값이 없으면 0을 반환함.
   < 결과 화면 >
-- # last_name, a의 첫번째 위치
 	'King' 		'0' -- King에서 a가 없으므로 0반환
 	'Kochhar'	'6'
 	'De Haan'	'5'
 	'Hunold'	'0'
	 'Ernst'	'0'

- upper(문자열) : 문자열을 대문자로 변환함.
- lower(문자열) : 문자열을 소문자로 변환함.

- left(문자열, 반환할 문자의 개수) : 문자열의 왼쪽에서부터 지정된 문자수만큼 반환함.
- right(문자열, 반환할 문자의 개수) : 문자열의 오른쪽에서부터 지정된 문자수만큼 반환함.
- substr(문자열, 시작위치, 반환할 문자의 개수) : 문자열의 일부분을 반환홤. 문자열에서 시작위치부터 지정된문자수만큼 반환함.

select  last_name, left(last_name, 2), right(last_name, 3),concat('***-****-', right(phone_number, 4)) -- 번호 뒷자리만 공개함.
from employees;
--  <결과화면>
-- last_name, left(last_name, 2), right(last_name, 3), phone
-------------------------------------------------------------
-- 'King', 	'Ki',	 'ing', 		'***-****-4567'
-- 'Kochhar', 	'Ko',	'har', 			'***-****-4568'
-- 'De Haan', 	'De',	'aan', 			'***-****-4569'

-- left(last_name, 5),right(last_name, 5) : 총 4글자라면 King은 이름이 다 공개됨.

select job_id, substr(job_id, 2, 3) from employees; 
-- job_id의 2번째에서 시작해서 3개의 문자를 가져와라.

-- substr == left
select first_name, substr(first_name, 1, 3), left(first_name, 3) from employees;

 

* substr == right 

-- substr 사용시 두번째 인수의 숫자가 음수(-)로 시작하면 끝에서부터 숫자의 갯수만큼 시작한다. 
-- -3이면 끝에서부터 3번재에서 시작한다는 뜻이다. 
select first_name, substr(last_name, -3, 3), right(last_name, 3) from employees;


-- 5글자가 안되는 글자에 -5를 하면 시작값을 찾기 못하기때문에 아무결과도 찾지 못한다. 
select first_name, substr(last_name, -5, 3), right(last_name, 3) from employees;

- lpad(문자열, 전체자리수, 채울 문자) : 문자열을 전체 자리수만큼 출력하되 남는 공간이 있다면 왼쪽에서부터 문자로 채워주는 함수. 오른쪽 정렬 함수.
- rpad(문자열, 전체자리수, 채울 문자) : 문자열을 전체 자리수만큼 출력하되 남는 공간이 있다면 오른쪽에서부터 문자로 채워주는 함수. 왼쪽 정렬 함수.

select lpad(last_name, 20, '_') as "L-name", rpad(first_name, 20, '_') as "F-name" from employees;

- ltrim(문자열) : 문자열의 왼쪽 공백을 제거함.
- rtrim(문자열) : 문자열의 오른쪽 공백을 제거함.
- trim(문자열)  : 문자열의 양쪽(앞/뒤)공백을 제거함. -- trim은 중간의 공백은 제거하지 않음.
- trim(방향 특정문자 from 문자열) : 방향 - leading(앞), trailing(뒤), both(양쪽)

                                                        문자열로부터 해당 방향의 특정 문자를 제거함. 

select ltrim('     SQL   문법      '), rtrim('     SQL   문법      '),trim('     SQL   문법      ');
select trim(both '_' from '____sql___문법______');

 

- replace(문자열, 특정문자, 다른문자) : 문자열로부터 특정문자를 다른문자로 교체해 주는 함수.

select employee_id, last_name, replace(phone_number, '.', '-') as phone from employees;

- space(길이) : 길이만큼의 공백을 반환해주는 함수.

select concat('MySQL', '          ', 'DBMS') as test1;
-- (==)
select concat('MySQL', space(10), 'DBMS') as test1;

 

* 오늘의 퀴즈 * 

 

1. employees 테이블로부터 사원들의 last_name과 last_name의 길이를 출력하되 last_name이 ‘J’, ‘A’, ‘M’으로 시작되는 사원만 출력하시오. 또한 last_name을 기준으로 오름차순 정렬해서 출력하시오.

select last_name, length(last_name) as Length -- 갯수가 아니고 길이임.
from employees
where  	last_name like "a%" 
or   	last_name like "j%" 
or 		last_name like "M%" 
 -- (권장 : 효율) where left(last_name, 1) in ('J', 'M', 'A')
 -- (권장 : 효율) where substr(last_name, 1, 1) in ('J', 'M', 'A') 
 -- (X) last_name in ('J%') (X) : %는 LIKE 랑만 사용이 가능하다.
 -- (X) like 는 문장의 길이가 길어지므로 위의 두 함수를 사용한 방법을 사용하는 것이 가독성이나 성능상으로 더 좋다. 
order by last_name;

2. employees 테이블로부터 last_name과 급여 액수를 별표(*)로 나타내는 query를 작성하시오. 각 별표는 $1,000를 의미하며 백단위 이하는 표시하지 않습니다. 또한 급여의 내림차순으로 데이터를 정렬하여 출력하고 컬럼 제목(column alias)을 EMPLOYEES_AND_THEIR_SALARIES로 지정하시오.
(천달러에 * 한개, 100달러이하는 *없음. ex.17000 이면 별표 17개, 9000 = 9개, 7800 = 7개)

-- =>나 : substr로 문자를 다 잘라내서 공백을 만든 후 -> lpad 함수로 별표를 찍어넣음.
-- select concat(last_name, space(20), lpad(substr(salary, 1, 0),floor(salary / 1000),'*'))  as "EMPLOYEES_AND_THEIR_SALARIES"
-- (권장 : 간단함.) 처음부터 rpad의 첫번째 열에 공백을 넣음.
select concat(last_name, space(5), rpad('', truncate(salary/1000,0), '*'))  as "EMPLOYEES_AND_THEIR_SALARIES"
from employees
order by salary desc;

 

---

* 숫자함수

- round(숫자, 반올림할 자리) : 숫자를 반올림할 자리까지 반올림함. ex. 월급계산
- truncate(숫자, 버림할 자리) : 숫자를 버림할 자리까지 남기고 버림함. ex.요금계산. 쇼핑몰 물건계산.
  숫자 : 1 2 3 . 4 5 6
  자리 :-2-1 0   1 2 3

select round(45.923, 2), round(45.923, 0), round(45.923, -1) ;
-- <결과>
-- round(45.923, 2), round(45.923, 0), round(45.923, -1)
--  '45.92',  		'46'			50

select truncate(45.923, 2), truncate(45.923, 0), truncate(45.923, -1) ;
-- <결과>
-- truncate(45.923, 2), truncate(45.923, 0), truncate(45.923, -1)
--  '45.92',  		'45',			'40'

- ceil(숫자) : 일의 자리까지 올림을 해주는 함수
- floor(숫자) : 일의 자리까지남기고 버림을 해주는 함수 (=) truncate(숫자, 0)

select ceil(45.923), ceil(52.1), floor(45.923), floor(52.1);

 

- mod(숫자1, 숫자2), 숫자1 mod 숫자2, 숫자1 % 숫자2 : 숫자1을 숫자2로 나눈 나머지를 반환함. 

select mod(157, 10), 157 mod 10, 157 % 10;

- abs(숫자) : 숫자의 절대값을 반환함. ex. 차익을 계산할때 500 - 1000 을 하면 음수가 나오니까 이떄 사용함.

select abs(-5), abs(5), abs(-4.5);

- power(숫자, 제곱값) = pow(숫자, 제곱값) : 숫자의 제곱값을 반환함. 

select power(2, 3), pow(8, 3), power(4, 2);

- sign(숫자) : 숫자가 양수이면 1, 음수이면 -1, 0이면 0을 반환함. 

select sign(3), sign(-3), sign(4.25), sign(-4.26), sign(0);

 

 

* 오늘의 문제 *

 1. employees 테이블로부터 last_name과 급여 액수를 별표(*)로 나타내는 query를 작성하시오.  각 별표는 $1,000를 의미하며 백단위 이하는 표시하지 않습니다.  또한 급여의 내림차순으로 데이터를 정렬하여 출력하고 컬럼 제목(column alias)을   EMPLOYEES_AND_THEIR_SALARIES로 지정하시오.
(천달러에 * 한개, 100달러이하는 *없음. ex.17000 이면 별표 17개, 9000 = 9개, 7800 = 7개)

-- select concat(last_name, space(20), lpad(substr(salary, 1, 0),floor(salary / 1000),'*'))  as "EMPLOYEES_AND_THEIR_SALARIES" -- 인수3개
select concat(last_name, space(5), rpad('', truncate(salary/1000,0), '*'))  as "EMPLOYEES_AND_THEIR_SALARIES"
-- (== ) select concat(last_name, space(5), rpad('*', truncate(salary/1000,0)+1, '*'))  as "EMPLOYEES_AND_THEIR_SALARIES" -- 인수2개 : 그래서 +1을 함. 공백을 하나 채우고 남는 공간을 별을 채워달라.????
-- truncate 함수를 안써도 결과는 나옴. but rpad로 별 출력하는데 2600/1000 = 2.6 = 3 (rpad, lpad는 실수가 오면 반올림을 한다.)
from employees
order by salary desc;

 2. 연봉(salary) 15% 인상, 1의 자리 까지 반올림.

-- (==) select employee_id, last_name, salary, round((salary * 0.155)+salary, 0) as "New Salary"
select employee_id, last_name, salary, round(salary * 1.155, 0) as "New Salary" from employees;