Oracle 转换函数
Oracle 转换函数 -- TO_CHAR(date|number {,fmt} {,nlsparams}) fmt:格式内容,返回的字符串是什么格式的,在此处指定;nlsparams:指定国家语言设置参数,设置一些数值中的字符,不同国家不同,默认按当前会话的设置
-- 将日期转换成字符串 -- fm:压缩因转换后字符串中多填充的空格或者数字前面填充的0 select employee_id,to_char(hire_date,'fmDD MONTH YYYY') from employees; select employee_id,to_char(hire_date,'DD-MM-YYYY') from employees; -- 特殊字符必须将特殊字符使用英文的双引号,引起来 select employee_id,to_char(hire_date,'YYYY"年"MM"月"DD"日"') from employees;
-- 将数字转换成字符串 select employee_id,salary,to_char(salary,'$999,999.00'),to_char(salary,'L999,999.00') from employees;
-- TO_DATE(char {,fmt} {,nlsparams}) 将字符串转换成日期类型 -- fx:精确匹配,要转换的字符串要严格的按照右边fmt的格式,否则就会报错 select last_name,hire_date from employees where hire_date>to_date('2007-09-10','fxYYYY-MM-DD'); -- TO_NUMBER(char {,fmt} {,nlsparams})将字符串转换成数值类型 select to_number('12,345.05','999,999.00') from dual; -- 函数嵌套 -- oracle 可以达到任意深度的函数嵌套,嵌套的计算是从内到外 select last_name,upper(concat(substr(last_name,1,3),'_ZH')) from employees; -- 通用函数 -- NVL(column|expression1,expression2) 将空值转换成目标值 expression2:如果expression1为空则返回expression2 -- 注:expression1和expression2的类型一定要一致 select last_name,salary,COMMISSION_PCT,(salary*12)+(salary*12*NVL(COMMISSION_PCT,0)) as "All_Salary" from employees; -- NVL2(expr1,expr2,expr3) 将空格转换为已指定的值 ,如果expr1不为空则返回 expr2,如果expr1为空则返回expr3 -- 注:expr1可以是任意类型,expr2和expr3可以是除LONG以外的任意类型 select last_name,salary,commission_pct,NVL2(COMMISSION_PCT,'SAL+COMM','SAL') as "InCome" from employees where DEPARTMENT_ID in (50,80); -- NULLIF(expr1,expr2) 判断expr1和expr2是否相等,如果相等则返回NULL,如果不等则返回expr1 -- 注:expr1和expr2不能直接传入NULL select LENGTH(first_name) as "expr1",LENGTH(last_name) as "expr2",NULLIF(LENGTH(first_name),LENGTH(last_name)) as "result" from employees; -- COALESCE(expr1,expr2,expr3,...exprn) 返回表达式中第一个不为空的表达式,如果expr1为空则判断expr2是否为空,如果不为空则返回expr2,否则较验expr3... -- 如果所有表达式都为空,则返回空;所有表达式都要是统一的数据类型 select last_name,COALESCE(to_char(COMMISSION_PCT),to_char(MANAGER_ID),'No commission and no manager') from employees; -- CASE expr WHEN expr1 THEN return1 -- [WHEN expr2 THEN return2 -- ... -- WHEN exprn THEN returnn] -- ELSE else_expr END select last_name,job_id,salary,Case job_id when 'IT_PROG' THEN 1.10*salary when 'ST_CLERK' THEN 1.15*salary when 'SA_REP' THEN 1.20*salary ELSE salary END AS "New Salary" from employees; select last_name,salary,(case when salary<5000 then 'Low' when salary<10000 then 'Medinum' when salary<20000 then 'good' ELSE 'Excellent' END) as "Salary Level" from employees; -- DECODE(expr,expr1,return1 -- expr2,return2 -- expr3,return3 -- ... -- default) select last_name,job_id,salary,decode(job_id,'IT_PROG',1.10*salary, 'ST_CLERK',1.15*salary, 'SA_REP',1.20*salary, salary) AS "New Salary" from employees;