SQL_함수 (문자함수, 문자열 조작함수)

📖 데이터베이스(databaase)에서 함수(function)

1. sql에서 함수(주로 내장함수) cf) 사용자 정의 함수도 있음

1) 컬럼의 값이나 데이터 타입을 변경하는 경우
2) 숫자 또는 날짜 데이터의 출력 형식을 변경하는 경우
3) 하나 이상의 행에 대한 집계(aggregation)를 하는 경우

2. sql 함수의 유형

1) 단일행 함수 : 테이블에 저장되어 있는 개별 행을 대상으로 하는 함수를 말하고, 적용을 하며 하나의 행당 하나의 결과를 반환하는 함수를 말한다.
  ex) 소문자/대문자 변환처리
2) 복수행 함수 : 조건에 따라 여러 행을 그룹화하여 그룹별로 결과를 하나씩 반환하는 함수
  여러 행의 데이터를 함수가 적용되었을 때, 단일 또는 줄어든 행의 결과값을 리턴하는 함수를 말한다.
  ex) 부서별 급여가 가장 적은 데이터, 직책별 인원수

3. 단일행 함수

1) 데이터 값을 조작하는데 주로 사용된다
2) 행별로 함수를 적용하여 하나의 결과를 변환하는 함수이다
3) 단일행 함수의 종류
  문자함수 : upper, lower, initcap
  숫자함수 : ceil, trunc, round
  날짜함수 : add_moths
  변환함수 : 묵시적/명시적 데이터 변환 - 문자열 ==> 숫자형, 숫자형 ==> 날짜형, 날짜형 ==> 문자열
  일반함수 : 일반함수 nvl, decode
4) 단일행 함수의 사용법
   select 함수명(컬럼명/데이터, 매개변수1, 매개변수2..
   where 컬럼 = 함수명(컬럼명/데이터, 매개변수1. 매개변수2..)
   함수(컬럼) = 데이터
   함수(컬럼) = 함수(컬럼)

📌문자함수

1. 문자 데이터를 입력하여 문자나 숫자를 결과롤 반환하는 함수

2. 문자 함수의 종류

  1) 대소문자 변환 함수
      initcap() : 첫번째 문자만 대문자로 변환 처리 함수
      lower() : 문자열 전체를 소문자로 변환
      upper() : 문자열 전체를 대문자로 변환

2) 문자조작 함수

  concat(문자열1, 문자열2) : 두 문자열을 결합처리 한다. cf) 문자열1 || 문자열2
  ex) concat('sql', 'plus') ==> sqlplus
                               ==> ||를 이용하는 경우가 많다.

  substr(문자열데이터, 시작위치, 시작위치에서 갯수) :
  문자열 데이터를 시작 위치와 마지막 위치를 기준을 절삭처리하여 사용하는 것을 말한다.
  index : 0부터 시작하는 경우/ 1부터 시작하는 경우
  oracle에서 substr은 1부터 시작
  ex) substr('sql^plus', 5,4) ==> plus

  양옆에 공백 문자에 처리
  ex) 사원명 : [AB ][검색]
  js, java, DB(trim(), ltrim(), rtrim())

  대소문자 구분 없이 검색되게 처리
  DB테이블의 Hello 검색하고 싶을 경우 => Hel시 해당 내용을 검색하지 않으면 검색이 안된다.
  Hello, hellO, hello, HELLO로도 검색이 되게 하려면
  where upper(ename) like '%' || upper('hellO') || '%'

  문자열 길이 반환 함수

• 문자열은 크기 문자 자체에 대한 갯수와 byte 단위로 크기를 처리하는 것으로
  나누어진다.
  영문은 1자가 1byte 한글은 oracle 기준으로 3bytes
• length(데이터/컬럼명) : 문자열의 길이(글자수)를 반환하는 함수, 한글/영문 동일
• length(데이터/컬럼명) : 문자열의 바이트를 반환하는 함수
  한글이나 특수문자인 경우 1글자당 3bytes인 경우가 있어 한글/영문이 차이가 난다.
  cf) 함수는 일반적으로 중첩하여 사용하는 경우가 많다.
  함수1(함수2(함수3(데이터, 매개변수),매개변수),매개변수)

SELECT ename, INITCAP(ename), lower(ename), upper(ename) 
FROM emp
WHERE lower(ename) LIKE '%a%'; 

-- ex) 함수를 이용하여 다음과 같은 형태로 출력하세요. 활용컬럼(ename, job), 조건문 소문자 변환하여 k 포함할 때. 
-- The job of Smith is a Clerk! ==> Smith, Clerk 컬럼 데이터
SELECT 'The job of ' || initcap(ename)|| ' is a ' || initcap(job) || '!' show
FROM EMP e 
WHERE lower(ename) LIKE '%k%';
--cf) WHERE ename = LIKE '%'||upper('k')||'%'; 

-- Q) 대소문자 구분없이 데이터를 검색하려면 어떻게 처리할 것인가?
-- 아래의 형식으로 함수를 적용해서 출력하되 ename이 대소문자 구분없이 a/A인 데이터로 
두번째 포함되어 검색 되게 처리하세요. 
-- The salary of Ward is @@@ won!!!
SELECT 'The salary of Ward is' || sal || 'won!!!' show
FROM EMP e 
WHERE lower(ename) LIKE '_a%' OR upper(ename) LIKE '_A%';

-- dual 가상테이블: 허나의 데이터를 테스트용으로 확인할 때 사용된다. 
SELECT * FROM dual;
SELECT '데이터1' 문자열, 25 숫자형, 25+30 연산, '안녕'||'하세요' 문자열연결 
FROM dual; 

-- concat(문자열1, 문자열2)
SELECT concat('좋은 ', '날 입니다. ^^') concat1 FROM dual;
SELECT concat(empno, ename) show FROM emp; 

-- substr(문자열데이터, 시작위치, 시작위치에서 갯수)
SELECT substr('sql*plus', 5, 4) substr01 FROM dual; -- 1부터 시작하는 5번째 p문자부터 4개를 추출해서 출력한다. 

-- ename의 첫번째에서 3번째 데이터 추출, job과 혼합하여 화면에 출력하되, substr(), concat()함수를 활용하세요
SELECT concat(substr(ename,1,3), job) show FROM emp; 

-- 951212-2701521 주민번호를 기준으로 substr를 활용하여 @@년 @@월 @@일 생일하고 표현하세요. 
SELECT
substr('951212-2701521',1,2) 연도, substr('951212-2701521',3,2) 월, substr('951212-2701521',5,2) 일, substr('951212-2701521',1,2) || '년 ' || substr('950425-2063632',3,2) || '월' || 
substr('950425-2063632',5,2) || '일 생일' 생일
FROM dual; 

-- 문자열 길이 length, lengthb
SELECT length('hello') str1, lengthb('hello') str2, length('안녕하세요') str3, lengthb('안녕하세요') str4
FROM dual;

SELECT ename, length(ename)
FROM emp;

-- ex) 사원명과 직책의 글자수를 사원명과 직책명과 함꼐 출력하되 직책의 글자수가 6 이상인 
경우만 검색하세요
SELECT * FROM emp;
SELECT ename, LENGTH(ename), job, LENGTH(job)
FROM EMP e 
WHERE LENGTH(job)>= 6;

📌 문자열 조작함수들

1. instr

• 특정 문자가 출현하는 첫번째 위치를 반환
  instr(데이터, '검색할 문자') : 해당 위치를 return
  instr(데이터, '검색할 문자', 검색할 시작위치, 매칭대상이 나올 횟수) :
  1부터 시작해서 검색할 시작위치와 검색되어 나온 데이터의 @@번째 횟수를 지정해서
  idex를 나오게 할 수 있다.

SELECT instr('SQL*Plus', '*') FROM dual;
SELECT instr('Welcome to Oracle 11g', 'o', 1, 1) FROM dual;
SELECT instr('Welcome to Oracle 11g', 'o', 3, 2) FROM dual; 
-- Welcome to Oracle 11g 데이터의 'o'로 검색할 때, 시작을 3부터하고, 두번째 o가 나오는 index 추출
-- 위의 경우 index 3부터 시작해서 두번째 o인 to에 있는 o의 위치를 리턴한다. 

      ex) 직책과 직책에서 'A'가 나오는 첫번째 위치를 검색하여 출력하세요.

SELECT job, instr(job, 'A') pos -- 0은 데이터가 나오지 않을 떄를 의미한다. 
FROM emp;

2. lpad(left padding), rpad(right padding)

• 전체 문자의 크기를 지정하고, 그 크기보다 못할 때, 왼쪽/오른쪽에 특정한 문자를 추가하게
  처리하는 기능을 말한다.

• 형식
  lpad(데이터, 크기지정, 덧붙일문자열)
  rpad(데이터, 크기지정, 덧붙일문자열)
  ps) 특정한 컬럼을 동일한 자리수를 만들거나, 가변형데이터를 고정형데이터로 변경할 때, 주로
  사용된다
  varchar2 ===> char

SELECT lpad('sql',5,'*')sh1,
rpad('sql', 5, '#') sh2
FROM dual; 

-- ename데이터를 고정형을 변환해서 등록
SELECT LENGTH(ename) FROM emp;
SELECT max(LENGTH(ename)) FROM emp;
SELECT ename, rpad(ename, 6, ' ') "공백삽입" FROM emp; 

-- ex) 사원명을 6자리, 직책명을 8자리로 설정하여, 사원명을 왼쪽에 #기호를, 직책명은 오른쪽에 @기호를 덧붙여 처리하여 출력하세요. 
SELECT lpad('ename',6,'#'),
rpad('job',8,'@')
FROM emp; --SELECT lpad('ename',6,'#')ename,
rpad('job',8,'@') job
FROM emp;

3. ltrim/rtrim

trim은 절삭이라는 말로, 오른쪽 또는 왼쪽에 있는 특정한 문자열을 삭제처리할 때 활용된다

• ltrim(데이터, '제거할문자') : 왼쪽에 제거할 문자를 없애주는데 반복적으로 제거가 된다.
• rtrim(데이터, '제거할문자') : 오른쪽에 제거할 문자를 없애주는데 반복적으로 제거가 된다.
• trim('양쪽삭제할문자', from 데이터) : 양쪽에 삭제할 문자를 한번에 제거 처리 한다.
  cf) char 고정형 데이터가 입력된 데이터가 크기가 다를때 varchar2 가변형데이터로 변환할
  때 활용할 수 있다.

SELECT ltrim('****sql****', '*') str1,
 	   rtrim('****sql****', '*') str2,
       trim('*' FROM '****sql****') str3
FROM dual;

-- ex) job은 왼쪽에 'S'를 삭제하고 출력하고, ename은 오른쪽에 'N'을 삭제하고 출력하세요. 
SELECT ltrim(job,'S') job2,
	   rtrim(ename,'N') ename2
FROM emp;