substr(字段,开始位置,取多少位);
decode(字段,'字段对应值1','翻译字段1','字段对应值2','翻译字段2','字段对应值3','翻译字段3'...)
to_char();
to_number();
length();--计算字符串长度;
vsize();--计算字符串字节数;
判断是否为中文的的一种方法:对应gbk字符集
select length(a.bank_account_name),
vsize(a.bank_account_name),
a.contract_id,
a.bank_account_name,
(case
when a.bank_account_neture = '对私' then
(case
when length(a.bank_account_name) = vsize(a.bank_account_name) then --判断是否为中文的的一种方法
a.bank_account_name
when length(a.bank_account_name) = 2 then
'x' ||
substr(a.bank_account_name, length(a.bank_account_name), 1)
when length(a.bank_account_name) = 3 then
substr(a.bank_account_name, 1, 1) || 'x' ||
substr(a.bank_account_name, length(a.bank_account_name), 1)
when length(a.bank_account_name) = 4 then
substr(a.bank_account_name, 1, 1) || 'x' || 'x' ||
substr(a.bank_account_name, length(a.bank_account_name), 1)
end)
else
a.bank_account_name
end) as bank_account_nameasd
from network_msg a;
to_date 是把字符串转换为数据库中的日期类型
例一:20080808080808 to 2008-08-08 08:08:08
select to_date(t.cometime, 'yyyy-mm-dd hh24:mi:ss') as times from table t
例二:20080808 to 2008-08-08
select to_date(t.gbirthday, 'yyyy-mm-dd') as gbirthday from table t
如果要在页面显示to_date后的参数,再to_char一下就可以在页面正确显示了:
select to_char(to_date(t.cometime, 'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')as times from table t
to_char 是把日期或数字转换为字符串
instr:
语法: instr(a1,a2,a3,a4); 返回被查找字符串的开始位置
a1:被查找的字符串 a2:要查找的字符串 a3:开始查找的位置 a4:指定在被查找的字符串中,第几次出现.
例子: instr('18-22-888-22','-',1,1) 返回第一次出现的位置,所以 = 3
substr:
语法:substr(a1,a2,a3); 返回取到的字符串
a1:被查找的字符串 a2 开始找的起始位置 a3 要找的个数
例子: substr('1122233344',1,4) 返回 1122