---
title: "oracle常见函数的使用"
author: "java1573"
date: "2007-08-01T16:41:50Z"
tags: []
slug: "oracle"
draft: false
lastmod: "2007-08-01T16:41:50Z"
id: 107814
url: "https://java1573.iteye.com/blog/107814"
---

--求各人姓名的长度  
\--规律：函数+"B":表示按字节计算，如lengthB  
select LengthB('abcd朗沃') from dual  
select t.stu\_name,length(t.stu\_name) from t\_stu t  
\--大小写转换  
select  upper(t.stu\_name),lower(t.stu\_name) from t\_stu t  
\--应用:用户名(不区别大小写)  
select count(\*) from t\_stu where lower(stu\_name)=lower('mike')  
select count(\*) from t\_stu where stu\_name='mike'  
\--字符串处理  
\--Substr(字符串，从哪开始取，取几个)  
select Substr('123abc456', 2, 3) from dual  
\--Instr(字符串，子串，从哪开始算，算第几次出现的  
select Instr('1ab3ab', 'ab', 3, 1)from dual

--要求姓名不显示LOVO  
select t.stu\_id,substr(t.stu\_name,5) stu\_name from t\_stu t  
where t.stu\_name like 'LOVO%'  
union all  
select t.stu\_id,t.stu\_name from t\_stu t  
where t.stu\_name not like 'LOVO%'

--类型转化  
\--数字转化为字符串  
\--要求：编号以数字打头，但有前置0保持为5位  
select to\_char(78,'\*\*\*\*\*\*') from dual  
select lpad('78',6,'\*') from dual

--日期转化为字符串(sysdate:system date,系统日期)  
select to\_char(sysdate,'yyyy.mm.dd') from dual  
select to\_date('2007.1.23','yyyy.mm.dd') from dual

--转化为数字  
select cast('1.156' as number(3,2)) from dual  
select cast(1/3 as number(3,2)) from dual  
\--数据导入导出时常用  
select to\_number('$6,102.23','$9,999.99') from dual;

--规律：要什么类型，就“to\_”什么类型。