--求各人姓名的长度
--规律:函数+"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_”什么类型。
--规律:函数+"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_”什么类型。