本文详细介绍SQL中处理字符串和日期的各种实用函数，包括substr、instr、concat、ltrim、rpad、lpad、length、lower、upper及日期函数如sysdate、trunc、add_months等，展示了如何在复杂的数据处理场景中运用这些函数解决问题。
- substr 函数：截取字符串
语法：SUBSTR(string，start， [length])
string：表示源字符串，即要截取的字符串。
start：开始位置，从1开始查找。如果start是负数，则从string字符串末尾开始算起。
length：可选项，表示截取字符串长度。
实例：
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'
- instr 函数：返回子字符串在源字符串中的位置
语法：INSTR(string，child_string，[start]，[show_time])
string：表示源字符串。
child_string：子字符串，即要查找的字符串。
start：可选项，开始位置，默认从1开始。如果为负数，则从右向左检索。
show_time：可选项，表示子字符串第几次出现在源字符串当中，默认第1次，负数则报错。
--表示从源字符串'city_company_staff'中第1个字符开始查找子字符串'_'第1次出现的位置
SELECT INSTR('city_company_staff', '_') FROM dual --返回5
--表示从源字符串'city_company_staff'中第5个字符开始查找子字符串'_'第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'中第3个字符开始查找子字符串'_'第2次出现的位置
SELECT INSTR('city_company_staff', '_', 3, 2) FROM dual --返回13
--start参数为-1，从右向左检索，查找'_'字符串在源字符串中第1次出现的位置
SELECT INSTR('city_company_staff', '_', -1, 1) FROM dual --返回13
--start参数为-6，从右向左检索，查找'_'字符串在源字符串中第2次出现的位置
SELECT INSTR('city_company_staff', '_', -6, 2) FROM dual --返回5
- substr 函数结合 instr 函数截取字符串
现有需求：数据查询处理需要对code进行"拆分"
code命名规则类似：城市_所属公司_员工职位_员工姓名
其中，城市、公司、职位、姓民字符串长度不固定，由于字符串长度不固定，只使用substr函数无法实现需求，需配合instr函数定位到字符'_'的位置，然后使用substr函数进行截取。详细见下面例子。
表数据如下：
城市获取 :
SELECT
SUBSTR (SOURCE_CODE, 1, INSTR (SOURCE_CODE, '_', 1, 1) - 1) AS CITY
FROM
TABLE_CODE_TEST
结果：
解释：此处截取源字符串SOURCE_CODE，从第1个字符开始，由于代表城市的code长度不固定，我们无法确定截取几个字符，所以使用instr函数判断第一个'_'字符的位置，进而确定每个SOURCE_CODE截取几个字符串。
那为什么减1呢？
因为INSTR (SOURCE_CODE, '_', 1, 1)获取的是源字符串中'_'字符第一次出现的位置，再减1就得出了CITY字符个数，因此在这里要减1。
公司获取：
SELECT
SUBSTR (
SOURCE_CODE,
INSTR (SOURCE_CODE, '_', 1, 1) + 1,
INSTR (SOURCE_CODE, '_', 1, 2) - INSTR (SOURCE_CODE, '_', 1, 1)-1
) AS COMPANY
FROM
TABLE_CODE_TEST
解释：截取源字符串，从（第一个'_'出现位置+1）开始，截取个数为：第2个'_'出现位置减去第1个'_'出现位置，此时还多了一个下划线'_'，再减去1即可得到代表公司字符串。
姓名获取:
SELECT
SUBSTR (SOURCE_CODE, INSTR (SOURCE_CODE, '_', 1, 3) + 1) AS STF_NAME
FROM
TABLE_CODE_TEST
结果如下：
解释：截取源字符串，从（'_'第3次出现位置+1）开始截取，截取到末尾。
- SYSDATE函数可以得到目前系统的时间
例如：
--得到当前日期
select sysdate from dual;
常用的格式如下：
--加法
select sysdate,add_months(sysdate,12) from dual; --加1年
select sysdate,add_months(sysdate,1) from dual; --加1月
select sysdate,to_char(sysdate+7,'yyyy-mm-dd HH24:MI:SS') from dual; --加1星期
select sysdate,to_char(sysdate+1,'yyyy-mm-dd HH24:MI:SS') from dual; --加1天
select sysdate,to_char(sysdate+1/24,'yyyy-mm-dd HH24:MI:SS') from dual; --加1小时
select sysdate,to_char(sysdate+1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual; --加1分钟
select sysdate,to_char(sysdate+1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual; --加1秒
--减法
select sysdate,add_months(sysdate,-12) from dual; --减1年
select sysdate,add_months(sysdate,-1) from dual; --减1月
select sysdate,to_char(sysdate-7,'yyyy-mm-dd HH24:MI:SS') from dual; --减1星期
select sysdate,to_char(sysdate-1,'yyyy-mm-dd HH24:MI:SS') from dual; --减1天
select sysdate,to_char(sysdate-1/24,'yyyy-mm-dd HH24:MI:SS') from dual; --减1小时
select sysdate,to_char(sysdate-1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual; --减1分钟
select sysdate,to_char(sysdate-1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual; --减1秒
SYSDATE函数取得当前日期是本月的第几周
select to_char(sysdate,'YYYYMMDD W HH24:MI:SS') from dual;
select to_char(sysdate,'W') from dual;
SYSDATE函数取得当前日期是一个星期中的第几天，星期日为第一天
select sysdate,to_char(sysdate,'D') from dual;
--获取当前年份
select to_char(sysdate,'yyyy') from dual;
--获取当前日期是第几季度
select to_char(sysdate,'Q') from dual;
--获取当前日期的月份
select to_char(sysdate,'mm') from dual;
--获取当前日期的日
select to_char(sysdate,'dd') from dual;
--ddd 年中的第几天
--WW 年中的第几个星期
--W 该月中第几个星期
--D 周中的星期几
--hh 小时(12)
--hh24 小时(24)
--Mi 分
--ss 秒
SYSDATE函数取当前日期是星期几，中文显示
--当前日期是星期几，中文显示
select to_char(sysdate,'day') from dual;
如果一个表在一个date类型的字段上面建立了索引，如何使用
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
- TRUNC函数
1.TRUNC（for dates）为指定元素而截去的日期值。
语法：TRUNC（date[fmt]）
date 一个日期值。
fmt 格式化日期格式，该日期将由指定的元素格式所截去。忽略它则由最近的日期截去。
实例：
--返回格式化的日期
TRUNC(TO_DATE('24-Nov-1999 08:00 pm'),'dd-mon-yyyy hh:mi am')
trunc（sysdate,'yyyy'） --返回当年第一天。
trunc（sysdate,'mm'） --返回当月第一天。
trunc（sysdate,'d'） --返回当前星期的第一天(上周末)。
trunc（sysdate,'dd'）--返回当前年月日。
trunc(sysdate, 'hh') --返回当前小时数。
trunc(sysdate, 'mi') --返回当前日期忽略秒数。
- TRUNC（for number）函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。
语法：
TRUNC(n1,n2)
n1表示被截断的数字;
n2表示要截断到那一位，默认值为0，n2可以是负数，表示截断小数点前。注意，TRUNC截断不是四舍五入。
--返回整数，去掉小数位(和trunc(123.458，0)结果一样)
select trunc(123.458) from dual; --123
--返回保留一位小数
select trunc(123.458,1) from dual;--123.40
--返回截取到小数点前一位
select trunc(123.458,-1) from dual; --120
- ADD_MONTHS
语法：ADD_MONTHS ( date,integer )
用法：
返回date加上integer个月后的日期。一个月是由会话(session)参数NLS_CALENDAR来定义的。
参数：
date是一个日期时间值或可以隐式转化为DATE的其他值。
integer是一个整数或其他可以转化为整数的值。返回值永远是DATE数据类型。如果date是一个月的最后一天，或者得到结果的月份的天数比date所在月份的天数时，返回结果是结果月份的最后一天。
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2011-01-31','YYYY-MM-DD'),1),'YYYY-MM-DD') result FROM dual;
---------------------------------
2011-02-28
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2011-01-27','YYYY-MM-DD'),1),'YYYY-MM-DD') result FROM dual;
---------------------------------
2011-02-27
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2011-01-31','YYYY-MM-DD'), 1.9),'YYYY-MM-DD') result FROM dual;
---------------------------------
2011-02-28
- ASCII
语法：ASCII ( char )
用法：
返回char的第一个字符在数据库字符集中的十进制表示。
参数：
char可以是CHAR，VARCHAR2，NCHAR，NVARCHAR2，或者能转化成它们的其他数据类型。
SELECT ASCII('甲骨文') result FROM dual;
-----------------------------------
42450
SELECTASCII('Oracle') result FROM dual;
-----------------------------------
79
- ASCIISTR
语法：ASCIISTR ( char )
用法：
返回字符串char在数据库字符集中的ASCII版本。非ASCII字符将会转化成\XXXX的格式，其中XXXX是UTF-16编码单元。
SELECT ASCIISTR('甲骨文') result FROM dual;
------------------------------
\7532\9AA8\6587
SELECT ASCIISTR('Oracle') result FROM dual;
------------------------
Oracle
- ASIN
语法：ASIN ( n )
用法：返回n的反正弦值。
n必须在-1到1之间。返回值的范围在-pi/2到pi/2。
参数：
n可以为任意数值类型或可以隐式转化成数值的其他类型。如果n是BINARY_FLOAT，则返回值的类型是BINARY_DOUBLE，否则返回值的类型与参数的数据类型相同。
SELECT ASIN(1)result FROM dual;
-------------------------------
1.57079633
- ATAN
语法：ATAN ( n )
用法：返回n的反正切值。n的取值没有范围；而返回值的范围在-pi/2到pi/2之间。
参数：
n可以为任意数值类型或可以隐式转化成数值的其他类型。如果n是BINARY_FLOAT，则返回值的类型是BINARY_DOUBLE，否则返回值的类型与参数的数据类型相同。
SELECT ATAN(1)result FROM dual;
--------------------------------
0.785398163
- ATAN2
语法：ATAN2 ( n1, n2 )
用法：返回n1与n2的反正切值。等同于ATAN(n1/n2)。
参数：
n可以为任意数值类型或可以隐式转化成数值的其他类型。如果n是BINARY_FLOAT，则返回值的类型是BINARY_DOUBLE，否则返回值的类型与参数的数据类型相同。
SELECT ATAN2(1,0)result FROM dual;
------------------------------
1.57079633
SELECT ATAN2(-0.1,0)result FROM dual;
------------------------------
-1.5707963
- AVG
语法：AVG ( [DISTINCT|ALL]expr )
用法：返回expr的平均值。
参数：
expr可以为任意数值类型或可以转化为数值类型的其他类型。返回值的数据类型与expr的数据类型相同。
SELECT AVG(DISTINCT sal) result FROM scott.emp;
----------------------------------------
2064.58333
SELECT AVG(ALL sal)result FROM scott.emp;
-------------------------------------
2073.21429
- BFILENAME
语法：BFILENAME ('directory', 'filename' )
用法：返回一个关于directory和filename的BFILE定位器。
参数：
directory是一个DIRECTORY对象。
filename是服务器文件系统的一个文件名。
- BIN_TO_NUM
语法：BIN_TO_NUM ( expr [,expr, ...] )
用法：将字节向量转化成等值的NUMBER。
参数：
每个expr不是0就是1。
SELECT BIN_TO_NUM(1,1,0,0) result FROM dual;
-----------------------------
12
- BITAND
语法：BITAND ( expr1,expr2 )
用法：返回expr1与expr2进行位与运算的结果。
参数：
expr1和expr2都是NUMBER，结果也是NUMBER。如果其中一个参数为NULL，结果也为NULL。
SELECT BITAND(BIN_TO_NUM(1,1,0,0),BIN_TO_NUM(1,0,1,1)) result FROM dual;
----------------------------------
8
- CONCAT
语法：CONCAT ( char1,char2 )
用法：返回值是char1连接char2，效果与连接符||一样。
参数：
char1和char2可以是CHAR，VARCHAR2，NCHAR，NVARCHAR2，CLOB或NCLOB。
select CONCAT('Hello ', 'Oracle') result from dual;
--------------------------------------
Hello Oracle
- CURRENT_DATE
语法：CURRENT_DATE
用法：
返回会话时区的当前日期时间（使用Gregorian日历）。
SELECT CURRENT_DATE FROM DUAL;
---------------------------------
2011/10/10 07:14:43
- CURRENT_TIMESTAMP
语法：CURRENT_TIMESTAMP (precision )
用法：
返回会话时区的当前日期和时间，包括小数秒。返回值类型是TIMESTAMP WITH TIME ZONE。如果没有指定precision，默认为6。LOCALTIMESTAMP和CURRENT_TIMESTAMP之间的区别是LOCALTIMESTAMP返回一个TIMESTAMP类型的值，而CURRENT_TIMESTAMP返回一个TIMESTAMP WITH TIME ZONE类型的值。
---返回会话中的时区
SELECTTO_CHAR(SESSIONTIMEZONE) result FROM DUAL;
-----------------------------
+08:00
---返回当前会话时区中的当前日期
SELECT TO_CHAR(CURRENT_TIMESTAMP) FROM DUAL;
------------------------------------
10-OCT-1104.07.39.860798 PM
---返回会话中的日期和时间
SELECTTO_CHAR(LOCALTIMESTAMP) FROM DUAL;
-------------------------------------
14-11月-08 12.35.37.453000 上午
- LTRIM函数:删除左边出现的字符串
语法：LTRIM(c1,c2)
用法：
C1 字符串；
c2 追加字符串,默认为空格；
select LTRIM(' gao qian jing',' ') text from dual;
--------------------------------------
gao qian jing
- RPAD函数：在字符串c1的右边用字符串c2填充，直到长度为n时为止
语法：RPAD(c1,n,c2)
用法：
c1 字符串
n 追加后字符总长度
c2 追加字符串,默认为空格
如果c1长度大于n，则返回c1左边n个字符；如果c1长度小于n，c1和c2连接后大于n，则返回连接后的左边n个字符；如果c1长度小于n，c1和c2连接后小于n，则返回c1与多个重复c2连接(总长度>=n)后的左边n个字符
select rpad('gao',10,'ab') from dual;
----------------------------
gaoabababa
- LPAD(c1,n,c2)函数：在字符串c1的左边用字符串c2填充，直到长度为n时为止
用法：
c1 字符串
n 追加后字符总长度
c2 追加字符串,默认为空格
如果c1长度大于n，则返回c1左边n个字符；如果c1长度小于n，c2和c1连接后大于n，则返回连接后的右边n个字符
select lpad('gao',10,'ab') from dual;
------------------------------------
abababagao
- LENGTH函数：返回字符串的长度;
语法：LENGTH(c1)
用法：C1 字符串
多字节符(汉字、全角符等)，按1个字符计算
select length('高乾竞'),length('北京市海锭区'),length('北京TO_CHAR') from dual;
-------------------------------------
3 6 9
- INSTR函数：在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
语法：INSTR(c1,c2,i,j)
用法：
c1 被搜索的字符串
c2 希望搜索的字符串
i 搜索的开始位置,默认为1
j 第J次出现的位置,默认为1
多字节符(汉字、全角符等)，按1个字符计算
select instr('oracle traning','ra',1,2) from dual;
-----------------------------------------
9
- LOWER函数：返回字符串并将字符串转为小写
语法：LOWER(x)
用法：x字符串
select lower('ORACLE') from dual;
-----------------------------
oracle
- UPPER函数：返回字符串并将字符串转为大写
语法：UPPER(x)
用法：字符串x并将字符串x转为大写
select upper('oracle') from dual;
----------------------------
ORACLE
- CONCAT函数：拼接两个字符串
语法：CONCAT(c1,c2)
用法：
c1字符串，c2 字符串
select CONCAT('ora','cle') from dual;
--------------------------------
oracle
-
EXTRACT函数：可处理dateTime和interval，并从中截取返回特定部分，如从timestamp中返回时区，从interval中返回天/小时、分钟等。
语法：
extract ({year| month | day | hour | minute | second } | { timezone_hour | timezone_minute } | { timezone_region | timezone_abbr } from { date_value | interval_value } )
1.从date类型(YYYY-MM-DD)中只能截取year,month,day
//获取年份
select extract(year from sysdate) from dual;
//获取月份
select extract(month from sysdate) from dual;
//获取日
select extract(day from sysdate) from dual;
select extract(year from date'2013-11-30') year from dual;
select extract(month from date'2013-11-30') month from dual;
select extract(day from date'2013-11-30') day from dual;
2.从timestamp中截取year,month,day,hour,minute ,second
//获取年份
select extract(year from systimestamp) year from dual;
//获取月份
select extract(month from systimestamp) month from dual;
//获取日
select extract(day from systimestamp) day from dual;
//获取分钟
select extract(minute from systimestamp) minute from dual;
//获取秒
select extract(second from systimestamp) second from dual;
//获取时区中的小时
select extract(timezone_hour from systimestamp) th from dual;
//获取时区中的分钟
select extract(timezone_minute from systimestamp) tm from dual;
//获取时区名称(英文)
select extract(timezone_region from systimestamp) tr from dual;
//获取时区名称简称(英文)
select extract(timezone_abbr from systimestamp) ta from dual;
3.计算两个日期的年份差、月份差、小时差、分钟差、秒数差
select extract(day from dt2-dt1) day
,extract(hour from dt2-dt1) hour
,extract(minute from dt2-dt1) minute
,extract(second from dt2-dt1) second
from
( select to_timestamp('2013-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1,to_timestamp('2013-11-30 22:08:46', 'yyyy-mm-dd hh24:mi:ss') dt2 from dual);
扫一扫
192
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
