Oracle SQL #4 문자 관련 함수,수 관련 함수, 날짜함수, 형변환 함수, 기타 함수

* 대소문자 변환함수 
- initcap : 첫번째 문자만 대문자. 나머지는 소문자 
- upper   : 모든 문자를 대문자로 변경
- lower   : 모든 문자를 소문자로 변경

 

ex) 학과번호가 101학과 학생의 이름 id 출력하기, 단 id는 첫문자만 대문자, 대문자, 소문자로 출력하기

select name,id,initcap(id), upper(id), lower(id) from student where deptno1 = 101;

ex) emp 테이블에서 사원이름이 smith인 사원의 이름, 급여,부서코드 조회. smith 이름은 대소문자에 상관없이 조회

select ename, sal, deptno from emp where ename = 'SMITH';
select ename, sal, deptno from emp where ename = upper('smith');	// smith를 대문자로 조회
select ename, sal, deptno from emp where lower(ename) = 'smith';	// ename을 소문자로. . .

*  문자열의 길이
- length   : 문자열의 길이
- lengthb  : 문자열의 byte 수. 
-      NAME     NOT NULL VARCHAR2(10) 
-      홍길동 : 문자열의 길이 : 3
-                 저장바이트 수 : 9 바이트
-                 한글을 한자 저장히기위한 필요 바이트 수는 3바이트.
-                 이것은 oracle DB의 기준이라 어떤 DB냐 따라 2바이트 or 3바이트 다름.

select name, id, length(id), lengthb(id), length(name), lengthb(name) from student;

*  부분문자열 : substr (컬럼명|문자열, 1부터 시작하는 인덱스,글자수)

ex) - 학생 중 생일이 3월인 학생의 이름, 생년월일

select name, jumin, substr(jumin,1,6) from student where substr(jumin,3,2) = '03' order by 3;

* instr : 문자열중 문자의 위치 값
      instr (컬럼|문자열, '문자', [시작인데스], [몇번째])

ex) instr('A*B*C','*',1,2) => 'A*B*C' 문자열 중 1번 인덱스부터 2번째 위치한 *의 문자 위치리턴

select instr('A*B*C','*',1,2) from dual;

ex)  학생테이블에서 이름, 전화번호 가져오는데 TEL컬럼의 ')'의 위치를 출력하기

select name, tel, instr(tel,')') "괄호의 위치" from student;

* 문자 추가함수
  lpad : 왼쪽에 문자 추가
  rpad : 오른쪽에 문자 추가
           lpad | rpad(컬럼 | 문자열, 전체자리수, 채울문자)
- lpad(id,10,'$') : id가 몇자리이든 총 10자리가 되게 왼쪽으로 $로 붙인다.

select name, lpad(id,10,'$'), grade from student;

 

- rpad(id,10,'$') : id가 몇자리이든 총 10자리가 되게 오른쪽으로 $로 붙인다.

select name, rpad(id,10,'$'), grade from student;

* 문자 제거함수
  trim : 양쪽 공백 제거  
  ltrim : 왼쪽의 설정 제거 ltrim(컬럼|문자열, '제거할 문자')
  rtrim : 오른쪽의 설정문자 제거 rtrim(컬럼|문자열, '제거할 문자')

select ltrim('   AAAA    ') a from dual;
select rtrim('   AAAA    ') b from dual;
select trim('   AAAA    ') c from dual;

*  수 관련 함수
  round 함수 : 반올림함수 round(컬럼|숫자, 출력할 소숫점 이하 자리수)

select round(12.5123) r1, round(12.5132,0) r2, round(12.5123,1) r3,
round(12.5123,2) r4, round(12.5123,-1) r5 from dual;

   trunc 함수 : 버림함수   trunc(컬럼|숫자, 출력할 소숫점 이하 자리수)

select trunc(12.5123) t1, trunc(12.5932,0) t2, trunc(12.5123,1) t3,  
trunc(12.5123,2) t4, trunc(12.5123,-1) t5 from dual;

  mod 함수 : 나머지 함수

select mod(12,10) from dual; 	// 나머지 값인 2가 조회.

 ceil 함수 : 큰 근사 정수       /       floor 함수 : 작은 근사정수

select  ceil(12.345), floor(12.345), ceil(-12.345),floor(-12.345) from dual;

- power 함수

select power(3,2) from dual; -- 3 * 3 
select power(3,3) from dual; -- 3 * 3 * 3

* 날짜 함수
 - months_between : 두날짜사이의 개월 수 리턴. 소숫점이 발생함
 - months_between(날짜1, 날짜2) : 날짜1 - 날짜2 의 개월 수 차이.

select months_between(sysdate,'22/01/19') from dual;  -- sysdate = 현재날짜 (22/03/19)

- last_day : 해당월의 마지막 날짜 리턴

select last_day('20/02/02') from dual;	--    20/02/29 조회

*형변환 함수

- 묵시적 형변환 : oracle이 자동으로 문자 '1'을 숫자 1로 형변환 

select 1 + '1' from dual; -- 실행은 되지만 성능이 좋지 않다. oracle 특성임

 

- 숫자  문자

select 1 + 'A' from dual; -- 실행 x. 'A'는 문자는 숫자로 묵시적 형변환 불가

- to_char : 숫자나 날짜형을 형식에 맞도록 문자열로 변환하는 함수

select sysdate, to_char(sysdate,'yyyy-mm-dd hh:mi:ss am') from dual;

select to_char(sysdate, 'yyyy') 년도1,  to_char(sysdate, 'rrrr') 년도2,
       to_char(sysdate, 'yy') 년도3,    to_char(sysdate, 'year') 년도4,
       to_char(sysdate, 'rr') 년도5,
       
       to_char(sysdate, 'mm') 월1,
       to_char(sysdate, 'mon') 월2, --약자표시
       to_char(sysdate, 'month') 월3, 
       
       to_char(sysdate, 'dd') 일1, -- 일자
       to_char(sysdate, 'day') 일2, --요일
       to_char(sysdate, 'ddth') 일3, --
       
       to_char(sysdate, 'hh') 시간1, -- 1 ~ 12
       to_char(sysdate, 'hh24') 시간2, -- 0 ~ 23
       to_char(sysdate, 'mi') 분1,   -- 분
       to_char(sysdate, 'ss') 초1,   -- 초
       to_char(sysdate, 'am') AM     -- 오전,오후
    from dual;

ex) emp 테이블에서 사원의 이름, 직업,  입사일, 입사 요일을 출력

select ename, job, hiredate, to_char(hiredate,'day') 입사요일 from emp;

 - to_char 숫자형식 바꾸기 '99999'에서 9는 숫자형을 말한다.

select to_char(1234.56,'99999') 숫자1, 
      -- 정수형 5자리로 출력      
      to_char(1234.56, '099999') 숫자2, 
      -- 정수형 6자리. 빈자리는 0으로 채운다      
      to_char(1234.56, '$99999') 숫자3,
      -- 정수형 6자리. 앞에 $표시.      
      to_char(1234.56, '99999.99') 숫자4,
      -- 자리수 7자리 수섯점이하 2자리포함      
      to_char(1234.56, '9,999.9') 숫자5,
      --자리수 6자리 소숫점  1자리, 세자리에 , 출력       
      to_char(1234.56, '$99,999') 숫자6            
      from dual;

 - to_date() : 형식화된 문자열을 날짜형으로 변환
 - to_number() : 숫자형 문자열을 숫자열로 변환

 

* 기타 함수

 - nvl() : nvl(컬럼값, 기본값) : 컬럼의 값이 null 인 경우 기본값으로 대체

ex) 교수의 이름 연봉 출력. 연봉 : 급여*12 + 보너스. 보너스가 없는경우는 보너스는 0으로 처리함.

select name,bonus, pay*12 + nvl(bonus,0) 연봉  from professor;