Sql中的单记录函数
1.ASSCII返回指定的字符对应的十进制数;
select ASCII('A'),ASCII('a'),ASCII('0') zero,ASCII(' ')space from dual;
A A zero space
--------------------------------------
65 97 48 32
2.CHR给出整数,返回对应的字符;
select chr(52725) zhao,chr(65) char65 from dual;
ZHAO CHAR65
---------------
王 A
3.CONCAT连接两个字符串;
select concat('10086','concat连接字符串')||'转人工' 电话 from dual;
电话
----------------
10086concat连接字符串转人工
4.INITCAP返回字符串并将字符串的第一字母大写;
select initcap('yuanqingbo') 姓名 from dual;
姓名
---------------
Yuanqingbo
5.INSTR(C1,C2,I,J)
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1:被搜索的字符串
C2:希望搜索的字符串
I:搜索的开始的位置,默认为1
J:出现的位置,默认为1
select instr('oracle traning','ra',1,2) instrr from dual;
INSTRRR
-------------
9
6.LENGTH
返回字符串长度:
select last_name,length(last_name),last_id,length(last_id) from employees;
LAST_NAME LENGTH(LAST_NAME) LAST_ID LENGTH(LAST_ID)
----------------------------------------------------------------
zhangsan 9 1 1
lisi 4 2 1
wangwu 6 3 1
4 1
7.lower
返回字符串,并将所有的字符小写
select last_name,lower(last_name) from employees;
LAST_NAME LOWER(LAST_NAME)
-----------------------------------------------
zhangsan zhangsan
lisi lisi
wangwu wangwu
8.UPPER
返回字符串,并将所有字符串大写
select LAST_NAME, UPPER(LAST_NAME) laster_name from employees;
LAST_NAME LASTER_NAME
--------------------------------------------
zhangsan ZHANGSAN
lisi LISI
wangwu WANGWU
9.RPAD和LPAD(粘贴字符)
RPAD 在列的右边粘贴字符
LPAD 在列的左边粘贴字符
select lpad(rpad('wang','2','#'),15,'*') aa from dual;
10.substr(string,start,count)
截取字符串,从start开始,count数量
11.replace('string','s1','s2')
String 希望被替换的字符或变量
s1 被替换的字符串
s2 要替换的字符串
select LAST_NAME, replace(LAST_NAME,'zh','11') LAST_NAME2 from employees;
LAST_NAME LAST_NAME2
-------------------------------------
zhangsan 11angsan
lisi lisi
wangwu wangwu
12.LEADING 剪掉前面的字符;
TRAILING 剪掉后面的字符;
13.返回大于或者等于给出数字最小整数;
select floor(s.salary),floor(12121.6555) aa from employees s
14.add_months
增加或减去月份
select to_char(ADD_MONTHS(TO_DATE(199712,'YYYYMM'),2),'YYYYMM') AA FROM DUAL;
SELECT TO_CHAR(ADD_MONTHS(TO_DATE(199712,'YYYYMM'),-2),'YYYYMM') AA FROM DUAL;
AA
------------
199710
15.last_day
返回日期的后一天
select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;
16.to_char(date,'dormat')
select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss')from dual;
AA
---------------------------------
2018.11.10 11:33:12
17.将给出的字符转成数字
select to_number('1994') from dual
AA
------------
1994
18.EMPTY_BLOB()和EMPTY_CLOB()
这两个函数都是用来对大数据类型字段进行初始化操作的函数;
19:user
返回当前用户的名字
select user from dual;