（五）单行函数

（五）单行函数

1.字符函数

常用字符函数讲解：

（1）取长度函数

length(string)

语句：

select length('ABCD') from dual;

（2）截取函数

substr(string,start, len)
string 表示源字符串，即要截取的字符串,
start 开始位置，从1开始查找。如果start是负数，则从string字符串末尾开始算起
len 表示截取字符串长度

select substr('Hello SQL!', 1) from dual;     --截取所有字符串，返回'Hello SQL!'
select substr('Hello SQL!', 2) from dual;   --从第2个字符开始，截取到末尾。返回'ello SQL!'
select substr('Hello SQL!', -4) from dual;    --从倒数第4个字符开始，截取到末尾。返回'SQL!'
select substr('Hello SQL!', 3, 6) from dual;  --从第3个字符开始，截取6个字符。返回'llo SQ'
select substr('Hello SQL!', -4, 3) from dual; --从倒数第4个字符开始，截取3个字符。返回'SQL'

（3）去除开头或者结尾的字符串

trim

ltrim

rtrim

select trim('S' from 'SXXS') from dual;    --去除首尾字符串
select trim ( '  sxsx  ' ) from dual;      --去除首尾空格
select ltrim ( '  sxsx  ' ) from dual;     --去除开头空格
select rtrim( ' sxsx   ' ) from dual;      --去除结尾空格
select ltrim('SSSXXS' , 'S') from dual;    --去除开头空格
select Rtrim('SSSXXX' , 'X') from dual;    --去除结尾空格

（4）替换函数
replace(源字符串，要被替换的字符串,要替换成的字符串)
translate (源字符串，要被替换的字符串,要替换成的字符串)

select replace('(1996)',')', '--') from dual;   --将')'替换成'--'
select translate('(1996)',')', '--') from dual; --将')'替换成'-'
select translate('(1996)','()', '--') from dual; -- 将'('替换成'-',将')'替换成'-'
select replace('(1996)','()','--') from dual;  -- 将'()'替换成'--'

replace 和 translate 都是替换，只不过，translate 是逐一替换对应的字符串，而replace则是寻找并替换整个字符串

（5）填充函数

lpad (s,len,char)

rpad(s,len,char)

s： 是要填充的字符串

len： 是填充后字符串的长度

char： 是用于填充的字符

如果 s 的长度小于 len，则在左侧填充 char 直到长度达到 len。如果 s 的长度已经大于等于 len，则返回原字符串 s。注意中文s和char位置若为中文，则一个中文占两个长度

select rpad('s',4,'*') from dual;
*select rpad('中',4,'*') from dual
select rpad('中',4,'国') from dual;

（6）字符查找函数

instr(string,child_string,start,show_time)

string：表示源字符串
child_string：子字符串，即要查找的字符串
start：可选项，开始位置，默认从1开始。如果为负数，则从右向左检索
show_time：可选项，表示子字符串第几次出现在源字符串当中，默认第1次，负数则报错

select INSTR('city_company_staff', '_') from dual    --返回5
--表示从源字符串'city_company_staff'中第1个字符开始查找子字符串'_'第1次出现的位置

select INSTR('city_company_staff', '_', 5) from dual    --返回5
--表示从源字符串'city_company_staff'中第5个字符开始查找子字符串'_'第1次出现的位置

  select INSTR('city_company_staff', '_', 5, 1) from dual    --返回5
--表示从源字符串'city_company_staff'中第5个字符开始查找子字符串'_'第1次出现的位置

  select INSTR('city_company_staff', '_', 3, 2) from dual    --返回13
--表示从源字符串'city_company_staff'中第3个字符开始查找子字符串'_'第2次出现的位置

  select INSTR('city_company_staff', '_', -1, 1) from dual    --返回13
-- start参数为-1，从右向左检索，查找'_'字符串在源字符串中第1次出现的位置

  select INSTR('city_company_staff', '_', -6, 2) from dual    --返回5
--start参数为-6，从右向左检索，查找'_'字符串在源字符串中第2次出现的位置

（7）连接函数

concat (string1,string2)

select concat('hello ','word!') from dual

（8）取ASCII码

ASCII(string)

select ascii('A') from dual

（9）大小写转换函数

upper(string)

lower(string)

initcap(string)

select upper('app ss') from  dual
select initcap('app ss') from  dual  -- 将每个字母的首字母大写
select lower('APP SS') from  dual

2.数值函数

常见数值函数讲解：

（1）四舍五入

ROUND(n,i)

• 四舍五入
• n是任意数值，是必需的。i是整数，是可选的，忽略则默认为0
• 若i为0，则取整数位四舍五入；若 i < 0, 则对n四舍五入取整到小数点左侧第i位；若 i > 0, 则对n四舍五入取整到小数点右侧第i位

select round(3.141592653) from dual
select round(3.141592653 , 0) from dual
select round(3.141592653 , -2) from dual
select round(53.141592653 , -2) from dual
select round(35.41592653 , -1) from dual
select round(3.141592653 , 4) from dual
select round(-5.6) from dual

（2）截断函数

TRUNC(n,i)

n是任意数值，是必需的。i是整数，是可选的，忽略则默认为0

select trunc(3.141592653) from dual
select trunc(3.141592653 , 0) from dual
select trunc(3.141592653 , -2) from dual
select trunc(53.141592653 , -2) from dual
select trunc(35.41592653 , -1) from dual
select trunc(3.141592653 , 4) from dual
select trunc(-5.6) from dual

（3）向上取整

ceil(n)

select ceil(3.1415926) from dual

（4）向下取整

floor(n)

select floor(3.1415926) from dual

（5）求余函数

mod(n1 , n2)

select  mod(9 , 3) from dual

（6）平方根函数

sqrt(n)

select sqrt(121) from dual

（7）幂函数

power(x,y) ，求 x 的 y 次幂

select power(11,2) from dual
select power(8 , 1/3) from dual

3.日期函数

常用日期函数讲解：

（1）我们用 sysdate 这个系统变量来获取当前日期和时间

select sysdate from dual  -- 系统的当前时间
select systimestamp  from dual -- 可以得到系统的当前时间，该时间包含时区信息，精确到微秒
select dbtimezone  from dual  -- 返回数据库时区

（2）加月函数

add_months(r,n)

r是指定日期，n是要增加的月份数，如果n为负数，则表示减去的月份数

如果指定的日期是月份的最后一天，返回的也是新的月份的最后一天，如果新的月份比指定的月份日期少，将会自动调回有效日期

select to_char(add_months(to_date('2018-11-12', 'yyyy-mm-dd'), 1),
'yyyy-mm-dd'),
to_char(add_months(to_date('2018-10-31', 'yyyy-mm-dd'), 1),
  'yyyy-mm-dd'),
to_char(add_months(to_date('2018-09-30', 'yyyy-mm-dd'), 1),
  'yyyy-mm-dd')
from dual

（3）月份最后一天

last_day(r)

select last_day(sysdate) from dual

（4）两个日期间相差的月份数

months_between(r1,r2)

函数返回r1日期和r2日期直接的月份。当r1>r2时，返回的是正数，假如r1和r2是不同月的同一天，则返回的是整数，否则返回的小数。当r1<r2时，返回的是负数

select months_between(to_date('2018-11-12', 'yyyy-mm-dd'),
to_date('2017-11-12', 'yyyy-mm-dd')) as zs, -- 整数
months_between(to_date('2018-11-12', 'yyyy-mm-dd'),
  to_date('2017-10-11', 'yyyy-mm-dd')) as xs, -- 小数
months_between(to_date('2017-11-12', 'yyyy-mm-dd'),
  to_date('2018-10-12', 'yyyy-mm-dd')) as fs -- 负数
from dual

（5）日期四舍五入

round(r,f)

日期r按f的格式进行四舍五入。如果f不填，则四舍五入到最近的一天

select sysdate, -- 当前时间
round(sysdate, 'yyyy') as year, -- 按年
round(sysdate, 'mm') as month, -- 按月
round(sysdate, 'dd') as day, -- 按天
round(sysdate) as mr_day, -- 不填默认按天
round(sysdate, 'hh24') as hour -- 按小时
from dual

（6）日期截取

将日期r按f的格式进行截取。如果f不填，则截取到当前的日期

select sysdate, -- 当前时间
trunc(sysdate, 'yyyy') as year, -- 按年
trunc(sysdate, 'mm') as month, -- 按月
trunc(sysdate, 'dd') as day, -- 按天
trunc(sysdate) as mr_day, -- 不填默认按天
trunc(sysdate, 'hh24') as hour -- 按小时
from dual

4.转换函数

常用转换函数讲解：

（1）数字转字符串

select TO_CHAR(1024) from dual

（2）日期转字符串

select TO_CHAR(sysdate,'yyyy-mm-dd') from dual

（3）字符串转日期

select TO_DATE('2017-01-01','yyyy-mm-dd') from dual

（4）字符串转数字

select to_number('100') from dual

5.其他函数

（1）空值处理函数

nvl(string1,string2)

nvl函数的括号中可以填两个数据，数据的数据类型必须相同，如果第一个数据为空，则取第二个数据的值，第一个数据不为空，则取第一个数据的原值。

select NVL(NULL,0) from dual

nvl2(string1,string2,string3)

nvl2函数的括号中可以填三个数据，如果数据1为空，返回数据3的值，如果数据1不为空，返回数据2的值

nullif(string1,string2)

如果数据1和数据2相等的话，返回null，如果不相等，返回数据1

coalesce(string1,string2,string3,stringn)

作用是返回第一个不为null的数据，如果都为null，则返回null，本质上类似于nvl函数的循环使用

（2）decode 函数

decode(条件,值 1,翻译值 1,值 2,翻译值 2,…值 n,翻译值 n,缺省值)
根据条件返回相应值

select  decode ('hl','hh','le','el') from dual