SQL 문자 관련함수

select initcap('student'), initcap(last_name), upper(last_name), lower(email) from employees

select 10+20 from dual

select sysdate from dual

select first_name || last_name||'님' "연결1", 

concat(concat(first_name,last_name),'님') "연결2", 

substr(first_name, 1,3) "앞글자3개", instr(first_name, 'a') "a위치", 

lpad(first_name, 10, '*') "왼쪽*채우기",  

rpad(first_name, 10, '*') "오른쪽*채우기",

ltrim(first_name, 's') "왼쪽s지우기", 

rtrim(first_name, 'n') "오른쪽n지우기",

length('민철') "이름길이", 

length('minchul') "이름길이",

lengthb('민철') "이름 byte길이 한글3", 

lengthb('minchul') "이름길이"

from employees

select round(123.547, 1) 반올림, trunc(123.17, 1) 절삭, mod(14, 9) "나머지", ceil(123.11122) "올림", floor(123.11111) "버림" from dual

포멧바꾸기

select first_name, hire_date, to_char(hire_date, 'yyyy/mm/dd') 일자, salary, to_char(salary, '99,999,999') sal,  department_id

 from employees

 where department_id = to_number('30')

 and hire_date = to_date('12/24/2005')

nvl(column, '')

nvl2(column, '')

 nullif(비교1, 비교2)

select first_name, hire_date, 

 nvl(to_char(department_id),'부서없음') nvl,

 nvl2(to_char(department_id),to_char(department_id),'부서없음') nvl2, 

 nullif('a','a') nulliftest,

 nullif('a','b') nulliftest,

 decode(department_id, 30, '30번이네요', 

                       90, salary*1.2, '아니네요') decodetest,

 (case when department_id=30 then salary*1.1

      when department_id=90 then salary*1.2  

      else salary

  end) casetest

from employees

select first_name, department_id, 

 decode(department_id/10, 9,  'A',

                          8,  'B',

                          7,  'C',

                          6,  'D', 'F') decodetest

 from employees

select first_name, department_id, 

 (case when department_id>=90 then 'A'

       when department_id>=80 then 'B'  

       when department_id>=70 then 'C'  

       when department_id>=60 then 'D'  

       else 'F'

  end) casetest

 from employees