函数

在值表达式中,除了可以使用常量、列名、集函数等之外,还可以使用函数作为组成成份。DM 中支持的函数分为数值函数、字符串函数、日期时间函数、空值判断函数、类型转换函数等。在这些函数中,对于字符串类型的参数或返回值,最大支持的长度为 32K-1。

本手册还给出了 DM 系统函数的详细介绍。下列各表列出了函数的简要说明。在本章各例中,如不特别说明,各例均使用示例库 BOOKSHOP,用户均为建表者 SYSDBA。

表8.1 数值函数
序号 函数名 功能简要说明
01 ABS(n) 求数值 n 的绝对值
02 ACOS(n) 求数值 n 的反余弦值
03 ASIN(n) 求数值 n 的反正弦值
04 ATAN(n) 求数值 n 的反正切值
05 ATAN2(n1,n2) 求数值 n1/n2 的反正切值
06 CEIL(n) 求大于或等于数值 n 的最小整数
07 CEILING(n) 求大于或等于数值 n 的最小整数,等价于 CEIL(n)
08 COS(n) 求数值 n 的余弦值
09 COSH(n) 求数值 n 的双曲余弦值
10 COT(n) 求数值 n 的余切值
11 DEGREES(n) 求弧度 n 对应的角度值
12 EXP(n) 求数值 n 的自然指数
13 FLOOR(n) 求小于或等于数值 n 的最大整数
14 GREATEST(n {,n}) 求一个或多个数中最大的一个
15 GREAT (n1,n2) 求 n1、n2 两个数中最大的一个
16 LEAST(n {,n}) 求一个或多个数中最小的一个
17 LN(n) 求数值 n 的自然对数
18 LOG(n1[,n2]) 求数值 n2 以 n1 为底数的对数
19 LOG10(n) 求数值 n 以 10 为底的对数
20 MOD(m,n) 求数值 m 被数值 n 除的余数
21 PI() 得到常数 π
22 POWER(n1,n2)/POWER2(n1,n2) 求数值 n2 以 n1 为基数的指数
23 RADIANS(n) 求角度 n 对应的弧度值
24 RAND([n]) 求一个 0 到 1 之间的随机浮点数
25 ROUND(n[,m]) 求四舍五入值函数
26 SIGN(n) 判断数值的数学符号
27 SIN(n) 求数值 n 的正弦值
28 SINH(n) 求数值 n 的双曲正弦值
29 SQRT(n) 求数值 n 的平方根
30 TAN(n) 求数值 n 的正切值
31 TANH(n) 求数值 n 的双曲正切值
32 TO_NUMBER (char [,fmt]) 将 CHAR、VARCHAR、VARCHAR2 等类型的字符串转换为 DECIMAL 类型的数值
33 TRUNC(n[,m]) 截取数值函数
34 TRUNCATE(n[,m]) 截取数值函数,等价于 TRUNC(n[,m])
35 TO_CHAR(n [, fmt [, 'nls' ] ]) 将数值类型的数据转换为 VARCHAR 类型输出
36 BITAND(n1, n2) 求两个数值型数值按位进行 AND 运算的结果
表8.2 字符串函数
序号 函数名 功能简要说明
01 ASCII(char) 返回字符对应的整数
02 ASCIISTR(char) 将字符串 char 中,非 ASCII 的字符转成\XXXX(UTF-16)格式,ASCII 字符保持不变
03 BIT_LENGTH(char) 求字符串的位长度
04 CHAR(n) 返回整数 n 对应的字符
05 CHAR_LENGTH(char)/ CHARACTER_LENGTH(char) 求字符串的串长度
06 CHR(n) 返回整数 n 对应的字符,等价于 CHAR(n)
07 CONCAT(char1,char2,char3,…) 顺序联结多个字符串成为一个字符串
08 DIFFERENCE(char1,char2) 比较两个字符串的 SOUNDEX 值之差异,返回两个 SOUNDEX 值串同一位置出现相同字符的个数。
09 INITCAP(char) 将字符串中单词的首字符转换成大写的字符
10 INS(char1,begin,n,char2) 删除在字符串 char1 中以 begin 参数所指位置开始的 n 个字符, 再把 char2 插入到 char1 串的 begin 所指位置
11 INSERT(char1,n1,n2,char2) / INSSTR(char1,n1,n2,char2) 将字符串 char1 从 n1 的位置开始删除 n2 个字符,并将 char2 插入到 char1 中 n1 的位置
12 INSTR(char1,char2[,n,[m]]) 从输入字符串 char1 的第 n 个字符开始查找字符串 char2 的第 m 次出现的位置,以字符计算
13 INSTRB(char1,char2[,n,[m]]) 从 char1 的第 n 个字节开始查找字符串 char2 的第 m 次出现的位置,以字节计算
14 LCASE(char) 将大写的字符串转换为小写的字符串
15 LEFT(char,n) / LEFTSTR(char,n) 返回字符串最左边的 n 个字符组成的字符串
16 LEN(char) 返回给定字符串表达式的字符(而不是字节)个数(汉字为一个字符),其中不包含尾随空格
17 LENGTH(char) 返回给定字符串表达式的字符(而不是字节)个数(汉字为一个字符),其中包含尾随空格
18 OCTET_LENGTH(char) 返回输入字符串的字节数
19 LOCATE(char1,char2[,n]) 返回 char1 在 char2 中首次出现的位置
20 LOWER(char) 将大写的字符串转换为小写的字符串
21 LPAD(char1,n,char2) 在输入字符串的左边填充上 char2 指定的字符,将其拉伸至 n 个字节长度
22 LTRIM(char1,char2) 从输入字符串中删除所有的前导字符,这些前导字符由 char2 来定义
23 POSITION(char1,/ IN char2) 求串 1 在串 2 中第一次出现的位置
24 REPEAT(char,n) / REPEATSTR(char,n) 返回将字符串重复 n 次形成的字符串
25 REPLACE(STR, search [,replace] ) 将输入字符串 STR 中所有出现的字符串 search 都替换成字符串 replace ,其中 STR 为 char、clob 或 text 类型
26 REPLICATE(char,times) 把字符串 char 自己复制 times 份
27 REVERSE(char) 将字符串反序
28 RIGHT / RIGHTSTR(char,n) 返回字符串最右边 n 个字符组成的字符串
29 RPAD(char1,n,char2) 类似 LPAD 函数,只是向右拉伸该字符串使之达到 n 个字节长度
30 RTRIM(char1,char2) 从输入字符串的右端开始删除 char2 参数中的字符
31 SOUNDEX(char) 返回一个表示字符串发音的字符串
32 SPACE(n) 返回一个包含 n 个空格的字符串
33 STRPOSDEC(char) 把字符串 char 中最后一个字符的值减一
34 STRPOSDEC(char,pos) 把字符串 char 中指定位置 pos 上的字符值减一
35 STRPOSINC(char) 把字符串 char 中最后一个字符的值加一
36 STRPOSINC(char,pos) 把字符串 char 中指定位置 pos 上的字符值加一
37 STUFF(char1,begin,n,char2) 删除在字符串 char1 中以 begin 参数所指位置开始的 n 个字符, 再把 char2 插入到 char1 串的 begin 所指位置
38 SUBSTR(char,m,n) / SUBSTRING(char FROM m [FOR n]) 返回 char 中从字符位置 m 开始的 n 个字符
39 SUBSTRB(char,n,m) SUBSTR 函数等价的单字节形式
40 TO_CHAR(character) 将 VARCHAR、CLOB、TEXT 类型的数据转化为 VARCHAR 类型输出
41 TRANSLATE(char,from,to) 将所有出现在搜索字符集中的字符转换成字符集中的相应字符
42 TRIM([LEADING|TRAILING|BOTH] [exp] [] FROM char2]) 删去字符串 char2 中由串 char1 指定的字符
43 UCASE(char) 将小写的字符串转换为大写的字符串
44 UPPER(char) 将小写的字符串转换为大写的字符串
45 REGEXP 根据符合 POSIX 标准的正则表达式进行字符串匹配
46 OVERLAY(char1 PLACING char2 FROM int [FOR int]) 字符串覆盖函数,用 char2 覆盖 char1 中指定的子串,返回修改后的 char1
47 TEXT_EQUAL 返回两个 LONGVARCHAR 类型的值的比较结果,相同返回 1,否则返回 0
48 BLOB_EQUAL 返回两个 LONGVARBINARY 类型的值的比较结果,相同返回 1,否则返回 0
49 NLSSORT(str1 [,nls_sort=str2]) 返回对自然语言排序的编码
50 GREATEST(char {,char}) 求一个或多个字符串中最大的字符串
51 GREAT (char1, char2) 求 char 1、char 2 中最大的字符串
52 to_single_byte (char) 将多字节形式的字符(串)转换为对应的单字节形式
53 to_multi_byte (char) 将单字节形式的字符(串)转换为对应的多字节形式
54 EMPTY_CLOB () 初始化 clob 字段
55 EMPTY_BLOB () 初始化 blob 字段
56 UNISTR (char) 将字符串 char 中,ascii 码(‘\XXXX’4 个 16 进制字符格式)转成本地字符。对于其他字符保持不变。
57 ISNULL(char) 判断表达式是否为 NULL
58 CONCAT_WS(delim, char1,char2,char3,…) 顺序联结多个字符串成为一个字符串,并用 delim 分割
59 SUBSTRING_INDEX(char, delim, count) 按关键字截取字符串,截取到指定分隔符出现指定次数位置之前
表8.3 日期时间函数
序号 函数名 功能简要说明
01 ADD_DAYS(date,n) 返回日期加上 n 天后的新日期
02 ADD_MONTHS(date,n) 在输入日期上加上指定的几个月返回一个新日期
03 ADD_WEEKS(date,n) 返回日期加上 n 个星期后的新日期
04 CURDATE() 返回系统当前日期
05 CURTIME(n) 返回系统当前时间
06 CURRENT_DATE() 返回系统当前日期
07 CURRENT_TIME(n) 返回系统当前时间
08 CURRENT_TIMESTAMP(n) 返回系统当前带会话时区信息的时间戳
09 DATEADD(datepart,n,date) 向指定的日期加上一段时间
10 DATEDIFF(datepart,date1,date2) 返回跨两个指定日期的日期和时间边界数
11 DATEPART(datepart,date) 返回代表日期的指定部分的整数
12 DAY(date) 返回日期中的天数
13 DAYNAME(date) 返回日期的星期名称
14 DAYOFMONTH(date) 返回日期为所在月份中的第几天
15 DAYOFWEEK(date) 返回日期为所在星期中的第几天
16 DAYOFYEAR(date) 返回日期为所在年中的第几天
17 DAYS_BETWEEN(date1,date2) 返回两个日期之间的天数
18 EXTRACT(时间字段 FROM date) 抽取日期时间或时间间隔类型中某一个字段的值
19 GETDATE(n) 返回系统当前时间戳
20 GREATEST(date {,date}) 求一个或多个日期中的最大日期
21 GREAT (date1,date2) 求 date1、date2 中的最大日期
22 HOUR(time) 返回时间中的小时分量
23 LAST_DAY(date) 返回输入日期所在月份最后一天的日期
24 LEAST(date {,date}) 求一个或多个日期中的最小日期
25 MINUTE(time) 返回时间中的分钟分量
26 MONTH(date) 返回日期中的月份分量
27 MONTHNAME(date) 返回日期中月分量的名称
28 MONTHS_BETWEEN(date1,date2) 返回两个日期之间的月份数
29 NEXT_DAY(date1,char2) 返回输入日期指定若干天后的日期
30 NOW(n) 返回系统当前时间戳
31 QUARTER(date) 返回日期在所处年中的季节数
32 SECOND(time) 返回时间中的秒分量
33 ROUND (date1[, fmt]) 把日期四舍五入到最接近格式元素指定的形式
34 TIMESTAMPADD(datepart,n,timestamp) 返回时间戳 timestamp 加上 n 个 datepart 指定的时间段的结果
35 TIMESTAMPDIFF(datepart,timeStamp1,timestamp2) 返回一个表明 timestamp2 与 timestamp1 之间的指定 datepart 类型时间间隔的整数
36 SYSDATE() 返回系统的当前日期
37 TO_DATE(CHAR[,fmt[,'nls']]) /TO_TIMESTAMP(CHAR[,fmt[,'nls']]) / TO_TIMESTAMP_TZ(CHAR[,fmt]) 字符串转换为日期时间数据类型
38 FROM_TZ(timestamp,timezone|tz_name]) 将时间戳类型 timestamp 和时区类型 timezone(或时区名称 tz_name)转化为 timestamp with timezone 类型
39 TZ_OFFSET(timezone| [tz_name]) 返回给定的时区或时区名和标准时区(UTC)的偏移量
40 TRUNC(date[,fmt]) 把日期截断到最接近格式元素指定的形式
41 WEEK(date) 返回日期为所在年中的第几周
42 WEEKDAY(date) 返回当前日期的星期值
43 WEEKS_BETWEEN(date1,date2) 返回两个日期之间相差周数
44 YEAR(date) 返回日期的年分量
45 YEARS_BETWEEN(date1,date2) 返回两个日期之间相差年数
46 LOCALTIME(n) 返回系统当前时间
47 LOCALTIMESTAMP(n) 返回系统当前时间戳
48 OVERLAPS 返回两个时间段是否存在重叠
49 TO_CHAR(date[,fmt[,nls]]) 将日期数据类型 DATE 转换为一个在日期语法 fmt 中指定语法的 VARCHAR 类型字符串。
50 SYSTIMESTAMP(n) 返回系统当前带数据库时区信息的时间戳
51 NUMTODSINTERVAL(dec,interval_unit) 转换一个指定的 DEC 类型到 INTERVAL DAY TO SECOND
52 NUMTOYMINTERVAL (dec,interval_unit) 转换一个指定的 DEC 类型值到 INTERVAL YEAR TO MONTH
53 WEEK(date, mode) 根据指定的 mode 计算日期为年中的第几周
54 UNIX_TIMESTAMP (datetime) 返回自标准时区的'1970-01-01 00:00:00 +0:00'的到本地会话时区的指定时间的秒数差
55 from_unixtime(unixtime) 返回将自'1970-01-01 00:00:00'的秒数差转成本地会话时区的时间戳类型
from_unixtime(unixtime, fmt) 将自'1970-01-01 00:00:00'的秒数差转成本地会话时区的指定 fmt 格式的时间串
56 SESSIONTIMEZONE 返回当前会话的时区
57 DBTIMEZONE 返回当前数据库的时区
57 DATE_FORMAT(d, format) 以不同的格式显示日期/时间数据
58 TIME_TO_SEC(d) 将时间换算成秒
59 SEC_TO_TIME(sec) 将秒换算成时间
60 TO_DAYS(timestamp) 转换成公元 0 年 1 月 1 日的天数差
61 DATE_ADD(datetime, interval) 返回一个日期或时间值加上一个时间间隔的时间值
62 DATE_SUB(datetime, interval) 返回一个日期或时间值减去一个时间间隔的时间值
表8.4 空值判断函数
序号 函数名 功能简要说明
01 COALESCE(n1,n2,…nx) 返回第一个非空的值
02 IFNULL(n1,n2) 当 n1 为非空时,返回 n1;若 n1 为空,则返回 n2
03 ISNULL(n1,n2) 当 n1 为非空时,返回 n1;若 n1 为空,则返回 n2
04 NULLIF(n1,n2) 如果 n1=n2 返回 NULL,否则返回 n1
05 NVL(n1,n2) 返回第一个非空的值
06 NULL_EQU 返回两个类型相同的值的比较
表8.5 类型转换函数
序号 函数名 功能简要说明
01 CAST(value AS 类型说明) 将 value 转换为指定的类型
02 CONVERT(类型说明,value) 将 value 转换为指定的类型
03 HEXTORAW(exp) 将 exp 转换为 BLOB 类型
04 RAWTOHEX(exp) 将 exp 转换为 VARCHAR 类型
05 BINTOCHAR(exp) 将 exp 转换为 CHAR
06 TO_BLOB(value) 将 value 转换为 blob
07 UNHEX(exp) 将十六进制的 exp 转换为格式字符串
08 HEX(exp) 将字符串的 exp 转换为十六进制字符串
表8.6 杂类函数
序号 函数名 功能简要说明
01 DECODE(exp, search1, result1, … searchn, resultn [,default]) 查表译码
02 ISDATE(exp) 判断表达式是否为有效的日期
03 ISNUMERIC(exp) 判断表达式是否为有效的数值
04 DM_HASH (exp) 根据给定表达式生成 HASH 值
05 LNNVL(condition) 根据表达式计算结果返回布尔值
06 LENGTHB(value) 返回 value 的字节数
07 FIELD(value, e1, e2, e3, e4...en) 返回 value 在列表 e1, e2, e3, e4...en 中的位置序号,不在输入列表时则返回 0
08 ORA_HASH(exp [,max_bucket [,seed_value]]) 为表达式 exp 生成 HASH 桶值

8.1 数值函数

数值函数接受数值参数并返回数值作为结果。

1.函数 ABS

语法:ABS(n)

功能:返回 n 的绝对值。n 必须是数值类型。

例 查询现价小于 10 元或大于 20 元的信息。

SELECT PRODUCTID,NAME FROM PRODUCTION.PRODUCT WHERE ABS(NOWPRICE-15)>5;

查询结果如下表 8.1.1 所示。

表8.1.1
PRODUCTID NAME
3 老人与海
4 射雕英雄传(全四册)
6 长征
7 数据结构(C 语言版)(附光盘)
10 噼里啪啦丛书(全 7 册)

2.函数 ACOS

语法:ACOS(n)

功能:返回 n 的反余弦值。n 必须是数值类型,且取值在-1 到 1 之间,函数结果从 0 到 π。

SELECT acos(0);

查询结果为:1.570796326794897E+000

3.函数 ASIN

语法:ASIN(n)

功能:返回 n 的反正弦值。 n 必须是数值类型, 且取值在-1 到 1 之间, 函数结果从-π/2 到 π/2。

SELECT asin(0);

查询结果为:0.000000000000000E+000

4.函数 ATAN

语法:ATAN(n)

功能:返回 n 的反正切值。n 必须是数值类型,取值可以是任意大小,函数结果从-π/2 到 π/2。

SELECT atan(1);

查询结果为:7.853981633974483E-001

5.函数 ATAN2

语法:ATAN2(n, m)

功能:返回 n/m 的反正切值。n,m 必须是数值类型,取值可以是任意大小,函数结果从-π/2 到 π/2。

SELECT atan2(0.2,0.3);

查询结果为:5.880026035475676E-001

6.函数 CEIL

语法:CEIL(n)

功能:返回大于等于 n 的最小整数。n 必须是数值类型。返回类型与 n 的类型相同。

SELECT CEIL(15.6);

查询结果为:16

SELECT CEIL(-16.23);

查询结果为:-16

7.函数 CEILING

语法:CEILING(n)

功能:返回大于等于 n 的最小整数。等价于函数 CEIL(n)。

8.函数 COS

语法:COS(n)

功能:返回 n 的余弦值。n 必须是数值类型,是用弧度表示的值。将角度乘以 π/180,可以转换为弧度值。

SELECT cos(14.78);

查询结果为:-5.994654261946543E-001

9.函数 COSH

语法:COSH(n)

功能:返回 n 的双曲余弦值。

SELECT COSH(0)"Hyperbolic cosine of 0";

查询结果为:1.000000000000000E+000

10.函数 COT

语法:COT(n)

功能:返回 n 的余切值。n 必须是数值类型,是用弧度表示的值。将角度乘以 π/180,可以转换为弧度值。

SELECT COT(20 * 3.1415926/180);

查询结果为:2.747477470356783E+000

11.函数 DEGREES

语法:DEGREES(n)

功能:返回弧度 n 对应的角度值,返回值类型与 n 的类型相同。

SELECT DEGREES(1.0);

查询结果为:5.729577951308238E+001

12.函数 EXP

语法:EXP(n)

功能:返回 e 的 n 次幂。

SELECT EXP(4) "e to the 4th power";

查询结果为:5.459815003314424E+001

13.函数 FLOOR

语法:FLOOR(n)

功能:返回小于等于 n 的最大整数值。n 必须是数值类型。返回类型与 n 的类型相同。

SELECT FLOOR(15.6);

查询结果为:15.0

SELECT FLOOR(-16.23);

查询结果为:-17.0

14.函数 GREATEST

语法:GREATEST(n {,n})

功能:求一个或多个数中最大的数。

SELECT GREATEST(1.2,3.4,2.1);

查询结果:3.4

15.函数 GREAT

语法:GREAT(n1,n2)

功能:求 n1、n2 中的最大的数。

SELECT GREAT (2, 4);

查询结果:4

16.函数 LEAST

语法:LEAST(n {,n})

功能:求一个或多个数中最小的一个。

SELECT LEAST(1.2,3.4,2.1);

查询结果:1.2

  1. 函数 LN
语法:LN(n)

功能:返回 n 的自然对数。n 为数值类型,且大于 0。

SELECT ln(95) "Natural log of 95";

查询结果为:4.553876891600541E+000

  1. 函数 LOG
语法:LOG(m[,n])

功能:返回数值 n 以数值 m 为底的对数;若参数 m 省略,返回 n 的自然对数。m,n 为数值类型,m 大于 0 且不为 1。

SELECT LOG(10,100);

查询结果为:2.000000000000000E+000

SELECT LOG(95);

查询结果为:4.553876891600541E+000

  1. 函数 LOG10
语法:LOG10(n)

功能:返回数值 n 以 10 为底的对数。n 为数值类型,且大于 0。

SELECT LOG10(100);

查询结果为:2.000000000000000E+000

  1. 函数 MOD
语法:MOD(m,n)

功能:返回 m 除以 n 的余数,当 n 为 0 时直接返回 m。m,n 为数值类型。

SELECT ROUND(NOWPRICE),mod(ROUND(NOWPRICE),10) FROM PRODUCTION.PRODUCT;

查询结果如下表 8.1.2 所示。

表8.1.2
ROUND(NOWPRICE) mod(ROUND(NOWPRICE),10)
15 5
14 4
6 6
22 2
20 0
38 8
26 6
11 1
11 1
42 2
  1. 函数 PI
语法:PI()

功能:返回常数 π。

SELECT PI();

查询结果为:3.141592653589793E+000

  1. 函数 POWER/POWER2
语法:POWER(m,n) / POWER2(m,n)

功能:返回 m 的 n 次幂。m,n 为数值类型,如果 m 为负数的话,n 必须为一个整数。其中 POWER()的返回值类型为 DOUBLE,POWER2()的返回值类型为 DECIMAL。

SELECT POWER(3,2) "Raised";

查询结果为:9.000000000000000E+000

SELECT POWER(-3,3) "Raised";

查询结果为:-2.700000000000000E+001

  1. 函数 RADIANS()
语法:RADIANS(n)

功能:返回角度 n 对应的弧度值,返回值类型与 n 的类型相同。

SELECT RADIANS(180.0);

查询结果为:3.141592653589790E+000

  1. 函数 RAND()
语法:RAND([n])

功能:返回一个[0,1]之间的随机浮点数。n 为数值类型,为生成随机数的种子,当 n 省略时,系统自动生成随机数种子。

SELECT RAND();

查询结果为一个随机生成的小数

SELECT RAND(314);

查询结果为:3.247169408246101E-002

  1. 函数 ROUND
语法:ROUND(n [,m])

功能:返回四舍五入到小数点后面 m 位的 n 值。m 应为一个整数,缺省值为 0,m 为负整数则四舍五入到小数点的左边,m 为正整数则四舍五入到小数点的右边。若 m 为小数,系统将自动将其转换为整数。

SELECT NOWPRICE,ROUND(NOWPRICE) FROM PRODUCTION.PRODUCT;

查询结果如下表 8.1.3 所示。

表8.1.3
NOWPRICE ROUND(NOWPRICE)
15.2000 15
14.3000 14
6.1000 6
21.7000 22
20.0000 20
37.7000 38
25.5000 26
11.4000 11
11.1000 11
42.0000 42
SELECT ROUND(15.163,-1);

查询结果为:20.0

SELECT ROUND(15.163);

查询结果为:15

  1. 函数 SIGN
语法:SIGN(n)

功能:如果 n 为正数,SIGN(n)返回 1,如果 n 为负数,SIGN(n)返回-1,如果 n 为 0,SIGN(n)返回 0。

SELECT ROUND(NOWPRICE),SIGN(ROUND(NOWPRICE)-20) FROM PRODUCTION.PRODUCT;

查询结果如下表 8.1.4 所示。

表8.1.4
ROUND(NOWPRICE) SIGN(ROUND(NOWPRICE)-20)
15 -1
14 -1
6 -1
22 1
20 0
38 1
26 1
11 -1
11 -1
42 1
  1. 函数 SIN
语法:SIN(n)

功能:返回 n 的正弦值。n 必须是数值类型,是用弧度表示的值。将角度乘以 π/180,可以转换为弧度值。

SELECT SIN(0);

查询结果为:0.0

  1. 函数 SINH
语法:SINH(n)

功能:返回 n 的双曲正弦值。

SELECT SINH(1);

查询结果为:1.175201193643801E+000

  1. 函数 SQRT
语法:SQRT(n)

功能:返回 n 的平方根。n 为数值类型,且大于等于 0。

SELECT ROUND(NOWPRICE),SQRT (ROUND(NOWPRICE)) FROM PRODUCTION.PRODUCT;

查询结果如下表 8.1.5 所示。

表8.1.5
ROUND(NOWPRICE) SQRT(ROUND(NOWPRICE))
15 3.872983346207417E+000
14 3.741657386773941E+000
6 2.449489742783178E+000
22 4.690415759823430E+000
20 4.472135954999580E+000
38 6.164414002968976E+000
26 5.099019513592785E+000
11 3.316624790355400E+000
11 3.316624790355400E+000
42 6.480740698407860E+000
  1. 函数 TAN
语法:TAN(n)

功能:返回 n 的正切值。n 必须是数值类型,是用弧度表示的值。将角度乘以 π/180,可以转换为弧度值。

SELECT TAN(45 *Pi()/180);

查询结果为:9.999999999999999E-001

  1. 函数 TANH
语法:TANH(n)

功能:返回 n 的双曲正切值。

SELECT TANH(0);

查询结果为:0.000000000000000E+000

  1. 函数 TO_NUMBER
语法:TO_NUMBER (char [,fmt])

功能:将 CHAR、VARCHAR、VARCHAR2 等类型的字符串转换为 DECIMAL 类型的数值。char 为待转换的字符串,fmt 为目标格式串。

若指定了 fmt 格式则转换后的 char 应该遵循相应的数字格式,若没有指定则直接转换成 DECIMAL。fmt 格式串一定要包容实际字符串的数据的格式,否则报错。无格式转换中只支持小数点和正负号。合法的 fmt 格式串字符如下表:

表8.1.6
元素 例子 说明
,(逗号) 9,999 指定位置处返回逗号 注意:1.逗号不能开头 2.不能在小数点右边
.(小数点) 99.99 指定位置处返回小数点
$ $9999 美元符号开头
0 0999 9990 以 0 开头,返回指定字符的数字 以 0 结尾,返回指定字符的数字
9 9999 返回指定字符的数字,如果不够正号以空格代替, 负号以-代替,0 开头也以空格代替。
D 99D99 返回小数点的指定位置,默认为’.’,格式串中最多能有一个 D
G 9G999 返回指定位置处的组分隔符,可有多个,但不能出现在小数点右边
S S9999 9999S 负值前面返回一个-号 正值前面不返回任何值 负值后面返回一个-号 正值后面不返回任何值 只能在格式串首尾出现
X XXXX xxxx 返回指定字符的十六进制值,如果不是整数则四舍五入到整数, 如果为负数则返回错误。
C C9999 返回指定字符的数字
B B9999 返回指定字符的数字

例 1 使用 9、G、D 来转换字符串'2,222.22'。

SELECT TO_NUMBER('2,222.22', '9G999D99');

查询结果:2222.22

例 2 使用 9、,(逗号)、.(小数点)来转换字符串'2,222.22'。

SELECT TO_NUMBER('2,222.22', '9,999.99');

查询结果:2222.22

例 3 使用$、9、,(逗号)、.(小数点)来转换字符串'2,222.22'。

SELECT TO_NUMBER('$2,222.22','$9G999D99');

查询结果:2222.22

例 4 使用 S、9 和.(小数点)来转换字符串'2,222.22'。

SELECT TO_NUMBER('-1212.12','S9999.99');

查询结果:-1212.12

例 5 使用 XXXX 来转换字符串'1,234'。

SELECT TO_NUMBER('1,234','XXXX');

查询结果:4660

例 6 无格式转换。

SELECT TO_NUMBER('-123.4');

查询结果:-123.4

  1. 函数 TRUNC
语法:TRUNC(n [,m])

功能:将数值 n 的小数点后第 m 位以后的数全部截去。当数值参数 m 为负数时表示将数值 n 的小数点前的第 m 位截去。当数值参数 m 省略时,m 默认为 0。

特殊说明:当 m 为负数,其绝对值大于或等于 n 的整数位个数时,结果取 0;当 m 取正数,其值大于等于 n 的小数位个数时,结果取 n。

SELECT SQRT(NOWPRICE), TRUNC(SQRT (ROUND(NOWPRICE) ),1)FROM PRODUCTION.PRODUCT;

查询结果如下表 8.1.7 所示。

表8.1.7
SQRT (NOWPRICE) TRUNC(SQRT (ROUND(NOWPRICE) ),1)
3.8987177379235853 3.8
3.7815340802378077 3.7
2.4698178070456938 2.4
4.658325879540846 4.6
4.47213595499958 4.4
6.1400325732035 6.1
5.049752469181039 5.0
3.3763886032268267 3.3
3.331666249791536 3.3
6.48074069840786 6.4
SELECT TRUNC(15.167,-1);

查询结果为:10.000

  1. 函数 TRUNCATE
语法:TRUNCATE(n [,m])

功能:等价于 TRUNC(n[,m])。将数值 n 的小数点后第 m 位以后的数全部截去。当数值参数 m 为负数时表示将数值 n 的小数点前的第 m 位截去。当数值参数 m 省略时,m 默认为 0。

特殊说明:当 m 为负数,其绝对值大于或等于 n 的整数位个数时,结果取 0;当 m 取正数,其值大于等于 n 的小数位个数时,结果取 n。

  1. 函数 TO_CHAR
语法:TO_CHAR(n [, fmt [, 'nls' ] ])

图例

函数 TO_CHAR(数值类型)

函数 TO_CHAR(数值类型)

语句功能:

将数值类型的数据转化为 VARCHAR 类型输出。其中:n 为数值类型的数据;fmt 为目标格式串。DM 的缺省格式为数字的字符串本身。如 SELECT
TO_CHAR(11.18),查询结果为:11.18。

fmt 中包含的格式控制符主要可以分为三类,具体如下如下:

  1. 主体标记;
  2. 前缀标记;
  3. 后缀标记。

其中主体标记包含的标记如表 8.1.8 所示。

表8.1.8 主体标记
格式控制符 说明
逗号(,) 逗号只能出现在整数部分的任意位置,如 to_char(1234, '9,99,9'), 结果为 1,23,4
点号(.) 作为小数点分隔符,不足的位数由后面的掩码决定
0 表示位数不足的时候用 0 填充,如 to_char(1234, '09999.00'), 结果为 01234.00
9 表示位数不足的时候用空格填充,如 to_char(1234, '9999,99'), 结果为’ 12,34’
D 作用同点号
G 作用同逗号
X 表示 16 进制
V 表示 10 的 n 次方
RN 转换为大写的罗马数字
rn 转换为小写的罗马数字

其中前缀标记包含的标记如表 8.1.9 所示。

表8.1.9 前缀标记
格式控制符 说明
FM 去掉前置空格
$ 美元符号。只能放在掩码最前面,且只能有一个
B 当整数部分的值为零时,返回空格
S 表示正负号, 如 to_char(1234, 'S9999')结果为 +1234, to_char(-1234,'S9999') 结果为-1234
TM9 64 个字符内返回原数值,超过则返回科学计数值
TME 返回科学计数值
C 当前货币名称缩写
L 当前货币符号

其中后缀标记包含的标记如表 8.1.10 所示。

表8.1.10后缀标记
格式控制符 说明
EEEE 科学计数符
MI 如'9999MI',如果是负数,在尾部加上负号(-); 如果是正数,则尾部加上空格
PR 将负数放到尖括号<>中
C 当前货币名称缩写
L 当前货币符号
S 表示正负号

这些标记的组合规则主要包括以下几个:

  1. 前缀之间的冲突;
  2. 后缀与前缀之间的冲突;
  3. 后缀之间的冲突。

其中,前缀之间的冲突如表 8.1.11 所示。

表8.1.11 前缀之间的冲突
前缀 与指定前缀存在冲突的前缀
$ $, C, L
B B
S $, B, S, C, L
TM9 $, B, S
TME $, B, S
FM $, B, TM9, TME
C C, L,$
L C, L,$

注:前缀之间的冲突指上表中第二列的前缀不能放在第一列的前缀之前。

如当前缀为 S 时,前缀中不能还有$, B, S, C, L 标记,即$S,BS,SS,CS,LS 不能作为前缀。类似,对于前缀 L,则 CL,LL,$L 不能作为前缀。

后缀与前缀之间的冲突如表 8.1.12 所示。

表8.1.12 后缀与前缀之间的冲突
后缀 与指定后缀存在冲突的前缀
L L, C,$
C L, C,$
$ $, C, L, MI, PR
S S
PR S
MI S

如当后缀为 C 时,前缀中不能还有 L, C, $等标记,如格式’L999C’等。

后缀之间的冲突如表 8.1.13 所示。

表8.1.13 后缀之间的冲突
后缀 与指定后缀存在冲突的后缀
EEEE S, , , PR
S S, MI, PR
PR S, MI, PR
MI S, MI, PR
C C, L, MI, PR, S, EEEE,$
L C, L, MI, PR, S, EEEE,$
$ $, MI, PR, S, C, L

注:后缀之间的冲突指上表中第二列的后缀不能放在第一列的后缀之前。

如当后缀为 L 时,后缀中不能还有 C、L、MI、PR、S、EEEE、¥ 等标记,即后缀 CL,LL,MIL,PRL,SL,EEEEL 不能在格式字符串中出现。

nls 用来指定以下数字格式元素返回的字符:

  1. 小数点字符;
  2. 组分隔符;
  3. 本地货币符号。
  4. 国际货币符号。

这个参数可以有这种形式:

'NLS_NUMERIC_CHARACTERS = ''dg''

NLS_CURRENCY = ''text''

NLS_ISO_CURRENCY = territory '

其中:
NLS_NUMERIC_CHARACTERS 参数指定字符 D 和 G 代表小数点字符和组分隔,必须用引号引起来。NLS_NUMERIC_CHARACTERS 串的长度只能是两个,并且这两个字符的不能相同;NLS_CURRENCY 指定的字符串用来代替本地货币符号,仅当 FMT 的前缀中有 L 时有效,不能超过 10 个字符的长度。nls 参数字符串如果包含空格,要用单引号括起来;如果包含单引号,也要用单引号括起来,以对单引号进行转义。

NLS_ISO_CURRENCY 用来指定的字符串用来代替国际货币符号,仅当 FMT 的前缀中有 C 时有效,取值只能是表 8.1.14 中的值,得到的结果是缩写的内容。

表8.1.14 NLS_ISO_CURRENCY的值及缩写形式
NLS_TERRITORY 缩写
CHINA CNY
TAIWAN TWD
AMERICA USD
UNITED KINGDOM GBP
CANADA CAD
FRANCE EUR
GERMANY EUR
ITALY EUR
JAPAN JPY
KOREA KRW
BRAZIL BRL
PORTUGAL EUR

举例说明

SELECT TO_CHAR('01110' + 1);

查询结果为:1111

SELECT TO_CHAR(-10000,'L99G999D99MI') "Amount";

查询结果为:

Amount

¥10,000.00-

CREATE TABLE T_INT (C1 INT);
INSERT INTO T_INT VALUES(456),(0),(213);
SELECT TO_CHAR(C1, 'L999D99MI',
   'NLS_NUMERIC_CHARACTERS = '';:''
   NLS_CURRENCY = ''AusDollars'' ') FROM T_INT;

查询结果为:

AusDollars456;00

AusDollars;00

AusDollars213;00

 SELECT TO_CHAR(C1, 'C999D99MI',
 'NLS_NUMERIC_CHARACTERS = '';:''
 NLS_CURRENCY = ''AusDollars'' 
 NLS_ISO_CURRENCY = ''TAIWAN'' ') FROM T_INT;

查询结果为:

TWD456;00

TWD;00

TWD213;00

  1. 函数 BITAND
语法:BITAND(n1, n2)

功能:返回两个数值型数值 n1 和 n2 按位进行 AND 运算后的结果。

特殊说明:当 n1 或 n2 是小数时,去掉小数点后做 AND 运算;如果 n1 或 n2 有一个是 0,则结果是 0;如果 n1 或 n2 有一个是 null,则结果是 null。

SELECT BITAND(-4, -5);

查询结果:-8

8.2 字符串函数

字符串函数一般接受字符类型(包括 CHAR 和 VARCHAR)和数值类型的参数,返回值一般是字符类型或是数值类型。

  1. 函数 ASCII
语法:ASCII(char)

功能:返回字符 char 对应的整数(ASSIC 值)。

SELECT ASCII('B') ,ASCII('中');

查询结果为:66 54992

  1. 函数 ASCIISTR
语法:ASCIISTR (char)

功能:将字符串 char 中,非 ASCII 的字符转成\XXXX(UTF-16)格式,ASCII 字符保持不变。

例 非 unicode 库下,执行如下操作:

SELECT CHr(54992),ASCIISTR('中') ,ASCIISTR(CHr(54992));

查询结果为:中 \4E2D \4E2D

  1. 函数 BIT_LENGTH
语法:BIT_LENGTH(char)

功能:返回字符串的位(bit)长度。

SELECT BIT_LENGTH('ab');

查询结果为:16

  1. 函数 CHAR
语法:CHAR(n)

功能:返回整数 n 对应的字符。

SELECT CHAR(66),CHAR(67),CHAR(68) , CHAR(54992);

查询结果为:B C D 中

  1. 函数 CHAR_LENGTH / CHARACTER_LENGTH
语法:CHAR_LENGTH(char) 或 CHARACTER_LENGTH(char)

功能:返回字符串 char 的长度,以字符作为计算单位,一个汉字作为一个字符计算。

字符串尾部的空格也计数。

SELECT NAME,CHAR_LENGTH(TRIM(BOTH ' ' FROM NAME))
FROM PRODUCTION.PRODUCT;

查询结果如下表 8.2.1 所示。

表8.2.1
NAME CHAR_LENGTH(TRIM(BOTH ' ' FROM NAME))
红楼梦 3
水浒传 3
老人与海 4
射雕英雄传(全四册) 10
鲁迅文集(小说、散文、杂文)全两册 17
长征 2
数据结构(C 语言版)(附光盘) 15
工作中无小事 6
突破英文基础词汇 8
噼里啪啦丛书(全 7 册) 11
SELECT CHAR_LENGTH('我们');

查询结果为:2

  1. 函数 CHR
语法:CHR(n)

功能:返回整数 n 对应的字符。等价于 CHAR(n)。

  1. 函数 CONCAT
语法:CONCAT(char1,char2,char3…)

功能:返回多个字符串顺序联结成的一个字符串,该函数等价于连接符||。

SELECT PRODUCTID,NAME, PUBLISHER, CONCAT(PRODUCTID,NAME,PUBLISHER) FROM PRODUCTION.PRODUCT;

查询结果如下表 8.2.2 所示:

表8.2.2
PRODUCTID NAME PUBLISHER CONCAT(PRODUCTID,NAME,PUBLISHER)
1 红楼梦 中华书局 1 红楼梦中华书局
2 水浒传 中华书局 2 水浒传中华书局
3 老人与海 上海出版社 3 老人与海上海出版社
4 射雕英雄传(全四册) 广州出版社 4 射雕英雄传(全四册)广州出版社
5 鲁迅文集(小说、散文、杂文)全两册 5 鲁迅文集(小说、散文、杂文)全两册
6 长征 人民文学出版社 6 长征人民出版社
7 数据结构(C 语言版)(附光盘) 清华大学出版社 7 数据结构(C 语言版)(附光盘)清华大学出版社
8 工作中无小事 机械工业出版社 8 工作中无小事机械工业出版社
9 突破英文基础词汇 外语教学与研究出版社 9 突破英文基础词汇外语教学与研究出版社
10 噼里啪啦丛书(全 7 册) 21 世纪出版社 10 噼里啪啦丛书(全 7 册)21 世纪出版社
  1. CONCAT_WS
语法:DIFFERENCE(char1,char2)

功能:比较两个字符串的 SOUNDEX 值之间的差异,返回两个 SOUNDEX 值串同一位置出现相同字符的个数。

SELECT DIFFERENCE('she', 'he');

查询结果为:3

  1. 函数 INITCAP
语法:INITCAP(char)

功能:返回句子字符串中,每一个单词的第一个字母改为大写,其他字母改为小写。单词用空格分隔,不是字母的字符不受影响。

SELECT INITCAP('hello world');

查询结果为:Hello World

  1. 函数 INS
语法: INS(char1,begin,n,char2)

功能:删除在字符串 char1 中以 begin 参数所指位置开始的 n 个字符,
再把 char2 插入到 char1 串的 begin 所指位置。

SELECT INS ('abcdefg',1,3, 'kkk') ;

查询结果为:kkkdefg

  1. 函数 INSERT / INSSTR
语法: INSERT(char1,n1,n2,char2) / INSSTR(char1,n1,n2,char2)

功能:将字符串 char1 从 n1 的位置开始删除 n2 个字符,并将 char2 插入到 char1 中 n1 的位置。

SELECT INSERT('That is a cake',2,3, 'his') ;

查询结果为:This is a cake

  1. 函数 INSTR
语法:INSTR(char1,char2[,n[,m]])

功能:返回 char1 中包含 char2 的特定位置。INSTR 从 char1 的左边开始搜索,开始位置是 n,如果 n 为负数,则搜索从 char1 的最右边开始,当搜索到 char2 的第 m 次出现时,返回所在位置。n 和 m 的缺省值都为 1,即返回 char1 中第一次出现 char2 的位置,这时与 POSITION 相类似。如果从 n 开始没有找到第 m 次出现的 char2,则返回 0。n 和 m 以字符作为计算单位,一个西文字符和一个汉字都作为一个字符计算。

此函数中 char1 和 char2 可以是 CHAR 或 VARCHAR 数据类型,n 和 m 是数值类型。

SELECT INSTR('CORPORATE FLOOR', 'OR', 3, 2) "Instring";

查询结果为:14

SELECT INSTR('我们的计算机', '计算机',1,1);

查询结果为:4

  1. 函数 INSTRB
语法:INSTRB(char1,char2[,n[,m]])

功能:返回从 char1 的第 n 个字节开始查找字符串 char2 的第 m 次出现的位置。INSTRB 从 char1 的左边开始搜索,开始位置是 n,如果 n 为负数,则搜索从 char1 的最右边开始,当搜索到 char2 的第 m 次出现时,返回所在位置。n 和 m 的缺省值都为 1,即返回 char1 中第一次出现 char2 的位置,这时与 POSITION 相类似。如果从 n 开始没有找到第 m 次出现的 char2,则返回 0。以字节作为计算单位,一个汉字根据编码类型不同可能占据 2 个或多个字节。

此函数中 char1 和 char2 可以是 CHAR 或 VARCHAR 数据类型,n 和 m 是数值类型。

SELECT INSTRB('CORPORATE FLOOR', 'OR', 3, 2) "Instring";

查询结果为:14

SELECT INSTRB('我们的计算机', '计算机',1,1);

查询结果为:7

  1. 函数 LCASE
语法:LCASE(char)

功能:返回字符串中,所有字母改为小写,不是字母的字符不受影响。

SELECT LCASE('ABC');

查询结果为:abc

  1. 函数 LEFT / LEFTSTR
语法:LEFT(char,n) / LEFTSTR(char,n)

功能:返回字符串最左边的 n 个字符组成的字符串。

SELECT NAME,LEFT(NAME,2) FROM PRODUCTION.PRODUCT;

查询结果如下表 8.2.3 所示。

表8.2.3
NAME LEFT(NAME,2)
红楼梦 红楼
水浒传 水浒
老人与海 老人
射雕英雄传(全四册) 射雕
鲁迅文集(小说、散文、杂文)全两册 鲁迅
长征 长征
数据结构(C 语言版)(附光盘) 数据
工作中无小事 工作
突破英文基础词汇 突破
噼里啪啦丛书(全 7 册) 噼里
SELECT LEFT ('computer science',10);

查询结果为:computer s

  1. 函数 LEN
语法:LEN(char)

功能:返回给定字符串表达式的字符(而不是字节)个数,其中不包含尾随空格。

SELECT LEN ('hi,你好□□');

查询结果为:5

说明:□ 表示空格字符

  1. 函数 LENGTH
语法:LENGTH(char)

功能:返回给定字符串表达式的字符(而不是字节)个数,其中包含尾随空格。

SELECT LENGTH('hi,你好□□');

查询结果为:7

说明:□ 表示空格字符

  1. 函数 OCTET_LENGTH
语法:OCTET_LENGTH(char)

功能:返回字符串 char 的长度,以字节作为计算单位,一个汉字根据编码类型不同可能占据 2 个或多个字节。

SELECT OCTET_LENGTH('大家好') "Length in bytes";

查询结果为:6

  1. 函数 LOCATE
语法:LOCATE(char1,char2[,n])

功能:返回字符串 char1 在 char2 中从位置 n 开始首次出现的位置,如果参数 n 省略或为负数,则从 char2 的最左边开始找。

SELECT LOCATE('man', 'The manager is a man', 10);

查询结果为:18

SELECT LOCATE('man', 'The manager is a man');

查询结果为:5

  1. 函数 LOWER
语法:LOWER(char)

功能:将字符串中的所有大写字母改为小写,其他字符不变。等价于 LCASE(char)。

  1. 函数 LPAD
语法:LPAD(char1,length[,char2])

功能:在字符串 char1 的左边,依次加入 char2 中的字符,直到总长度达到 length,返回增加后的字符串。如果未指定 char2,缺省值为空格。length 为正整数。如果 length 的长度比 char1 大,则返回 char2 的前(length-length(char1))个字符 +char1,总长度为 length。如果 length 比 char1 小,则返回 char1 的前 lengh 个字符。长度以字节作为计算单位,一个汉字作为二个字节计算。

注:若 length 为小于或等于零的整数,则返回 NULL。

SELECT LPAD(LPAD('FX',19,'Teacher'),22,'BIG') "LPAD example";

查询结果为:BIGTeacherTeacherTeaFX

SELECT LPAD('计算机',8, '我们的');

查询结果为:我计算机

  1. 函数 LTRIM
语法:LTRIM(char[,set])

功能:删除字符串 char 左边起,出现在 set 中的任何字符,当遇到不在 set 中的第一个字符时结果被返回。set 缺省为空格。

SELECT LTRIM('xyyxxxXxyLAST WORD', 'xy') "LTRIM example";

查询结果为:XxyLAST WORD

SELECT LTRIM('我们的计算机', '我们');

查询结果为:的计算机

  1. 函数 POSITION
语法:POSITION(char1 IN char2) / POSITION(char1, char2)

功能:返回在 char2 串中第一次出现的 char1 的位置,如果 char1 是一个零长度的字符串,POSITION 返回 1,如果 char2 中 char1 没有出现,则返回 0。以字节作为计算单位,一个汉字根据编码类型不同可能占据 2 个或多个字节。

SELECT POSITION('数' IN '达梦数据库');

查询结果为:5

  1. 函数 REPEAT / REPEATSTR
语法:REPEAT(char,n) / REPEATSTR(char,n)

功能:返回将字符串重复 n 次形成的字符串。

SELECT REPEAT ('Hello ',3);

查询结果为:Hello Hello Hello

  1. 函数 REPLACE
语法:REPLACE(STR, search [,replace])

功能:STR 为 CHAR、CLOB 和 TEXT 类型,search 和 replace 为字符串类型。在字符串 STR 中找到字符串 search,替换成 replace。若 replace 为空,则在 char 中删除所有 search。

SELECT NAME,REPLACE(NAME, '地址', '地点') FROM PERSON.ADDRESS_TYPE;

查询结果如下表 8.2.4 所示。

表8.2.4
NAME REPLACE(NAME, '地址', '地点')
发货地址 发货地点
送货地址 送货地点
家庭地址 家庭地点
公司地址 公司地点
  1. 函数 REPLICATE(char,times)
语法:REPLICATE(char,times)

功能:把字符串 char 自己复制 times 份。

SELECT REPLICATE('aaa',3);

查询结果为:aaaaaaaaa

  1. 函数 REVERSE
语法:reverse(char)

功能:将输入字符串的字符顺序反转后返回。

例:

SELECT REVERSE('abcd');

查询结果:dcba

  1. 函数 RIGHT / RIGHTSTR
语法:RIGHT(char,n) / RIGHTSTR(char,n)

功能:返回字符串最右边 n 个字符组成的字符串。

SELECT NAME, RIGHT (NAME,2) FROM PERSON.ADDRESS_TYPE;

查询结果如下表 8.2.5 所示。

表8.2.5
NAME RIGHT (NAME,2)
发货地址 地址
送货地址 地址
家庭地址 地址
公司地址 地址
SELECT RIGHTSTR('computer',3);

查询结果为:ter

  1. 函数 RPAD
语法:RPAD(char1,length[,char2])

功能:返回值为字符串 char1 右边增加 char2,总长度达到 length 的字符串,length 为正整数。如果未指定 char2,缺省值为空格。如果 length 的长度比 char1 大,则返回 char1+char2 的前(length-length(char1))个字符,总长度为 length。如果 length 比 char1 小,则返回 char1 的前 lengh 个字符。长度以字节作为计算单位,一个汉字作为二个字节计算。

注:若 length 为小于或等于零的整数,则返回 null。

SELECT RPAD('',11, 'BigBig') "RPAD example";

查询结果为:FUXINBigBig

SELECT RPAD('计算机',8, '我们的');

查询结果为:计算机我

  1. 函数 RTRIM
语法:RTRIM(char1[,set])

功能:删除字符串 char1 右边起出现的 set 中的任何字符,当遇到不在 set 中的第一个字符时结果被返回。set 缺省为空格。

SELECT RTRIM('TURNERyXxxxyyyxy', 'xy') "RTRIM e.g.";

查询结果为:TURNERyX

SELECT RTRIM('我们的计算机','我计算机');

查询结果为:我们的

  1. 函数 SOUNDEX
语法:SOUNDEX(char)

功能:返回一个表示英文字符串发音的字符串,由四个字符构成,第一个为英文字符,后三个为数字。NULL 返回 NULL,当 INI 参数 COMPATIBLE_MODE=0 或 2 时,将忽略原字符串中所有非英文字符,若原字符串为空串或者不存在英文字符则返回 NULL;当 COMPATIBLE_MODE=3 时,遇到非英文字符则不再处理后续字符,若原字符串为空串或者不存在英文字符则返回"0000"。

SELECT SOUNDEX('Hello');

查询结果为:H400

  1. 函数 SPACE
语法:SPACE(n)

功能:返回一个包含 n 个空格的字符串。

SELECT SPACE(5);

查询结果为:□□□□□

SELECT CONCAT(CONCAT('Hello',SPACE(3)), 'world');

查询结果为:Hello□□□world

说明:□ 表示空格字符

  1. 函数 STRPOSDEC
语法:STRPOSDEC(char)

功能:把字符串 char 中最后一个字符的值减一。

SELECT STRPOSDEC('hello');

查询结果为:helln

  1. 函数 STRPOSDEC
语法:STRPOSDEC(char,pos)

功能:把字符串 char 中指定位置 pos 上的字符的值减一。

SELECT STRPOSDEC('hello',3);

查询结果为:heklo

  1. 函数 STRPOSINC
语法:STRPOSINC(char)

功能:把字符串 char 中最后一个字符的值加一。

SELECT STRPOSINC ('hello');

查询结果为:hellp

  1. 函数 STRPOSINC
语法:STRPOSINC (char,pos)

功能:把字符串 char 中指定位置 pos 上的字符的值加一。

SELECT STRPOSINC ('hello',3);

查询结果为:hemlo

  1. 函数 STUFF

语法:STUFF(char1,begin,n,char2)

功能:删除在字符串 char1 中以 begin 参数所指位置开始的 n 个字符,再把 char2 插入到 char1 的 begin 所指位置。

SELECT STUFF('ABCDEFG',1,3, 'OOO');

查询结果为:OOODEFG

  1. 函数 SUBSTR/SUBSTRING
语法:SUBSTR(char[,m[,n]]) / SUBSTRING(char[ from m [ for n ]])

功能:返回 char 中从字符位置 m 开始的 n 个字符。若 m 为 0,则把 m 就当作 1 对待。若 m 为正数,则返回的字符串是从左边到右边计算的;反之,返回的字符是从 char 的结尾向左边进行计算的。如果没有给出 n,则返回 char 中从字符位置 m 开始的后续子串。如果 n 小于 0,则返回 NULL。如果 m 和 n 都没有给出,返回 char。函数以字符作为计算单位,一个西文字符和一个汉字都作为一个字符计算。

SELECT NAME,SUBSTRING(NAME FROM 3 FOR 2) FROM PRODUCTION.PRODUCT;

查询结果如下表 8.2.6 所示。

表8.2.6
NAME SUBSTRING(NAME FROM 3 FOR 2)
红楼梦
水浒传
老人与海 与海
射雕英雄传(全四册) 英雄
鲁迅文集(小说、散文、杂文)全两册 文集
长征
数据结构(C 语言版)(附光盘) 结构
工作中无小事 中无
突破英文基础词汇 英文
噼里啪啦丛书(全 7 册) 啪啦
SELECT SUBSTR('我们的计算机',3,4) "Subs";

查询结果为:的计算机

  1. 函数 SUBSTRB
语法:SUBSTRB(string,m[,n])

功能:返回 char 中从第 m 字节位置开始的 n 个字节长度的字符串。若 m 为 0,则 m 就当作 1 对待。若 m 为正数,则返回的字符串是从左边到右边计算的;若 m 为负数,返回的字符是从 char 的结尾向左边进行计算的。若 m 大于字符串的长度,则返回空串。如果没有 n,则缺省的长度为整个字符串的长度。如果 n 小于 1,则返回 NULL。

这里假设字符串 string 的长度为 len,如果 n 的值很大,超过 len – m,则返回的子串的长度为 len – m。

如果开始位置 m 不是一个正常的字符的开始位置,那么返回的结果是 k 个空格(k 的值等于下一个有效字符的开始位置和 m 的差),空格后面是有效字符;如果字符串的 m+n-1 的位置不是一个有效的字符,那么就以空格填充。也就是不截断字符。

SELECT SUBSTRB('达梦数据库有限公司',4,15);

查询结果为:□ 数据库有限公司

说明:□ 表示空格字符,下同。

字符串前面是一个空格,这是因为字符串'达梦数据库有限公司'的第 4 个字节不是一个完整的字符的开始,因此用空格代替。

SELECT SUBSTRB('我们的计算机',3,4) "Subs", LENGTHB(SUBSTRB('我们的计算机',3,4));

查询结果为:们的 4

SELECT SUBSTRB('ABCDEFG',3,3) "Subs";

查询结果为:CDE

注意:函数 SUBSTRB 字节作为计算单位,一个字符在不同的编码方式下的字节长度是不同的。

  1. 函数 TO_CHAR
语法: TO_CHAR(character)

图例

函数 TO_CHAR(VARCHAR、CLOB、TEXT 类型)

函数 TO_CHAR(VARCHAR、CLOB、TEXT 类型)

功能:将 VARCHAR、CLOB、TEXT 类型的数据转化为 VARCHAR 类型输出。VARCHAR 类型的长度不能超过 32767 个字节,CLOB、TEXT 类型的长度不能超过 32766 个字节。

当参数类型为 VARCHAR 时,还可指定 FMT 与 NLS,FMT 和 NLS 的具体意义和限制可参见 8.1 节的 TO_CHAR 函数介绍。

SELECT TO_CHAR('0110');

查询结果为:0110

CREATE TABLE T2(C1 VARCHAR(4000));

INSERT INTO T2 VALUES('达梦数据库有限公司成立于2000年,为国有控股的基础软件企业,专业从事数据库管理系统研发、销售和服务。其前身是华中科技大学数据库与多媒体研究所,是国内最早从事数据库管理系统研发的科研机构。达梦数据库为中国数据库标准委员会组长单位,得到了国家各级政府的强力支持。');

SELECT TO_CHAR(C1) FROM T2;

查询结果为:达梦数据库有限公司成立于 2000 年,为国有控股的基础软件企业,专业从事数据库管理系统研发、销售和服务。其前身是华中科技大学数据库与多媒体研究所,是国内最早从事数据库管理系统研发的科研机构。达梦数据库为中国数据库标准委员会组长单位,得到了国家各级政府的强力支持。

SELECT TO_CHAR('123','99,99','NLS_ISO_CURRENCY=CHINA');

查询结果为:1,23

  1. 函数 TRANSLATE
语法:TRANSLATE(char,from,to)

功能:TRANSLATE 是一个字符替换函数。char、from 和 to 分别代表一字符串。对于 char 字符串,首先,查找 char 中是否含有 from 字符串,如果找到,则将其含有的 from 与 to 中的字符一一匹配,并用 to 中相应的字符替换,直至 from 中的字符全部替换完毕。to 中的不足或多余的字符,均视为空值。

SELECT TRANSLATE('我们的计算机', '我们的', '大世界');

查询结果为:大世界计算机 ('我'将被'大'替代,'们'将被'世'替代,'的'将被'界'替代)

SELECT TRANSLATE('我们的计算机', '我们的', '世界');

查询结果为:世界计算机('我'将被'世'替代,'们'将被'界'替代,'的'对应的是空值,将被移走)

SELECT TRANSLATE('我们的计算机', '我们的', '大大世界');

查询结果为:大大世计算机 ('我'将被'大'替代,'们'将被'大'替代,'的'将被'世'替代,'界'对应的是空值,将被忽略)

SELECT NAME,TRANSLATE (NAME,'发货','送货') FROM PERSON.ADDRESS_TYPE;

查询结果如下表 8.2.7 所示。

表8.2.7
NAME TRANSLATE (NAME,'发货','送货')
发货地址 送货地址
送货地址 送货地址
家庭地址 家庭地址
公司地址 公司地址
  1. 函数 TRIM
语法:TRIM([<LEADING|TRAILING|BOTH> [char1] FROM ] char2)

功能:TRIM 从 char2 的首端(LEADING)或末端(TRAILING)或两端(BOTH)删除 char1 字符,如果任何一个变量是 NULL,则返回 NULL。默认的修剪方向为 BOTH,默认的修剪字符为空格。

SELECT NAME,TRIM(TRAILING '址' FROM NAME) FROM PERSON.ADDRESS_TYPE;

查询结果如下表 9.2.8 所示。

表9.2.8
NAME TRIM(TRAILING '址' FROM NAME)
发货地址 发货地
送货地址 送货地
家庭地址 家庭地
公司地址 公司地
SELECT TRIM( ' Hello World ');

查询结果为:Hello World

SELECT TRIM(LEADING FROM ' Hello World ');

查询结果为:Hello World□□□

说明:□ 表示空格字符,下同。

SELECT TRIM(TRAILING FROM ' Hello World ');

查询结果为:□□□Hello World

SELECT TRIM(BOTH FROM ' Hello World ');

查询结果为:Hello World

  1. 函数 UCASE
语法:UCASE(char)

功能:返回字符串中,所有字母改为大写,不是字母的字符不受影响。

SELECT UCASE('hello world');

查询结果为:HELLO WORLD

  1. 函数 UPPER
语法:UPPER(char)

功能:返回字符串中,所有字母改为大写,不是字母的字符不受影响。等价于 UCASE(char)。

  1. 函数 REGEXP

REGEXP 函数是根据符合 POSIX 标准的正则表达式进行字符串匹配操作的系统函数,是字符串处理函数的一种扩展。使用该函数时需要保证 DM 安装目录的 bin 子目录下存在 libregex.dll(windows)或 libregex.so(linux)库文件,否则报错。

达梦支持的匹配标准如下:

表8.2.9 符合POSIX标准的正则表达式
语法 说明 示例
. 匹配任何除换行符之外的单个字符 d.m 匹配“dameng”
* 匹配前面的字符 0 次或多次 a*b 匹配“bat”中的“b”和“about”中的“ab”。
+ 匹配前面的字符一次或多次 ac+ 匹配包含字母“a”和至少一个字母“c”的单词,如“race”和“ace”。
^ 匹配行首 ^car 仅当单词“car”显示为行中的第一组字符时匹配该单词
$ 匹配行尾 end$ 仅当单词“end”显示为可能位于行尾的最后一组字符时匹配该单词
[] 字符集,匹配任何括号间的字符 be[n-t] 匹配“between”中的“bet”、“beneath”中的“ben”和“beside”中的“bes”,但不匹配“below”中的“bel”。
[^] 排除字符集。匹配任何不在括号间的字符 be[n-t] 匹配“before”中的“bef”、“behind”中的“beh”和“below”中的“bel”,但是不匹配“beneath”中的“ben”。
(表达式) 在表达式加上括号或标签在替换命令中使用 (abc)+ 匹配“abcabcabc”
| 匹配 OR 符号 (|) 之前或之后的表达式。最常用在分组中。 (sponge|mud) bath 匹配“sponge bath”和“mud bath”。
\ 按原义匹配反斜杠 (\) 之后的字符。这使您可以查找正则表达式表示法中使用的字符,如 { 和 ^。 \^ 搜索 ^ 字符
{n[,m]} 区间表达式,匹配在它之前的单个字符重现的次数区间。{n}指重复 n 次;{n,}为至少出现 n 次重复;{n,m}为重现 n 至 m 次 zo{2} 匹配“zoone”中的“zoo”,但不匹配“zozo”。
[[:alpha:]] 表示任意字母([a-z]+)| ([A-Z]+)
[[:digit:]] 表示任意数字\d ([0-9]+)
[[:lower:]] 表示任意小写字母 ([a-z]+)
[[:alnum:]] 表示任意字母和数字([a-z0-9]+)
[[:space:]] 表示任意空格\s
[[:upper:]] 表示任意大写字母([A-Z]+)
[[:punct:]] 表示任意标点符号
[[:xdigit:]] 表示任意 16 进制数([0-9a-fA-F]+)
\w 表示一个数字或字母字符
\W 表示一个非数字或字母字符
\s 表示一个空格字符
\S 表示一个非空格字符
\d 表示一个数字字符
\D 表示一个非数字字符

值得注意的是,对于 Perl 规则的正则表达式达梦暂不支持:[==],{n}?, \A, \Z, *?,+?, ??, {n}?, {n,}?, {n,m}?。

DM8 支持的 REGEXP 函数如下表:

表8.2.10 REGEXP函数
序号 函数名 功能简要说明
1 REGEXP_COUNT(str, pattern[, position [, match_param]]) 根据 pattern 正则表达式,从 str 字符串的第 position 个字符开始查找符合正则表达式的子串的个数,并符合匹配参数 match_param
2 REGEXP_LIKE(str, pattern [, match_param]) 根据 pattern 正则表达式,查找 str 字符串是否存在符合正则表达式的子串,并符合匹配参数 match_param
3 REGEXP_INSTR(str, pattern[, position[, occurrence [, return_opt [, match_param [, subexpr]]]]]) 根据 pattern 正则表达式,从 str 字符串的第 position 个字符开始查找符合 subexpr 正则表达式的子串,如果 return_opt 为 0,返回第 occurrence 次出现的位置,如果 return_opt 为大于 0,则返回该出现位置的下一个字符位置,并符合匹配参数。Subexpr 为匹配的子 pattern。
4 REGEXP_SUBSTR(str, pattern [,position [, occurrence [,match_param[, subexpr]]]]) 根据 pattern 正则表达式,从 str 字符串的第 position 个字符开始查找符合 subexpr 正则表达式的子串,返回第 occurrence 次出现的子串,并符合匹配参数 match_param。
5 REGEXP_REPLACE(str, pattern [, replace_str [, position [, occurrence [,match_param]]]]) 根据 pattern 正则表达式,从 str 字符串的第 position 个字符开始查找符合正则表达式的子串,并用 replace_str 进行替换第 occurrence 次出现的子串,并符合匹配参数 match_param。

参数说明:

str:待匹配的字符串,最大长度为 8188 字节;

pattern:符合 POSIX 标准的正则表达式,最大长度为 512 字节;

position:匹配的源字符串的开始位置,正整数,默认为 1;

occurrence:匹配次数,正整数,默认为 1;

match_param:正则表达式的匹配参数,默认大小写敏感,如下表所示:

表8.2.11 匹配参数
说明
c 表示大小写敏感。例如:REGEXP_COUNT('AbCd', 'abcd', 1, 'c'),结果为 0。
i 表示大小写不敏感。例如:REGEXP_COUNT('AbCd', 'abcd', 1, 'i'),结果为 1。
m 将源字符串当成多行处理,默认当成一行。 例如:REGEXP_COUNT('ab'||CHR(10)||'ac', '^a.', 1, 'm'),结果为 2。
n 通配符(.)匹配换行符,默认不匹配。 例如:REGEXP_COUNT('a'||CHR(10)||'d', 'a.d', 1, 'n'),结果为 1。
x 忽略空格字符。例如:REGEXP_COUNT('abcd', 'a b c d', 1, 'x'),结果为 1。

return_opt:正整数,返回匹配子串的位置。值为 0:表示返回子串的开始位置;值大于 0:表示返回子串结束位置的下一个字符位置;

subexpr:正整数,取值范围为:0~9,表示匹配第 subexpr 个子 pattern 正则表达式,子 pattern 必须是括号的一部分。如果 subexpr=0,则表示匹配整个正则表达式;如果 subexpr>0,则匹配对应的第 subexpr 个子 pattern;如果 subexpr 大于子 pattern 个数,则返回 0;如果 subexpr 为 NULL,则返回 NULL。

replace_str:用于替换的字符串,最大长度为 512 字节。

DM 的 REGEXP 函数支持正则表达式的反向引用,通过“\数字”的方式进行引用,如\1 表示第一个匹配的子表达式。

如下详细介绍各函数:

  1. 函数 REGEXP_COUNT
语法:REGEXP_COUNT(str, pattern[, position [, match_param]])

功能:根据 pattern 正则表达式,从 str 字符串的第 position 个字符开始查找符合正则表达式的子串的个数,并符合匹配参数 match_param。position 默认值为 1,position 为正整数,小于 0 则报错;如果 position 为空,则返回 NULL。pattern 必须符合正则表达式的规则,否则报错。match_param 不合法,则报错。

返回值:如果 str、pattern 和 match_param 其中有一个为空串或 NULL,则返回 NULL。如果不匹配,返回 0;如果匹配,返回匹配的个数。

SELECT REGEXP_COUNT('AbCd', 'abcd', 1, 'i') FROM DUAL;

查询结果:1

SELECT REGEXP_COUNT('AbCd', 'abcd', 1, 'c') FROM DUAL;

查询结果:0

  1. 函数 REGEXP_LIKE
语法:REGEXP_LIKE(str, pattern [, match_param])

功能:根据 pattern 正则表达式,查找 str 字符串是否存在符合正则表达式的子串,并符合匹配参数 match_param。

返回值:如果匹配,则返回 TRUE;否则返回 FALSE。如果 str、pattern 和 match_param 中任一个为空串或 NULL,则返回 NULL;

SELECT 1 FROM DUAL WHERE REGEXP_LIKE('DM database V7', 'dm', 'c');

查询结果:无返回行

SELECT 1 FROM DUAL WHERE REGEXP_LIKE('DM database V7', 'dm', 'i');

查询结果:1

  1. 函数 REGEXP_INSTR
语法:REGEXP_INSTR(str, pattern[, position[, occurrence [, return_opt [,match_param [, subexpr]]]]])

功能:根据 pattern 正则表达式,从 str 字符串的第 position 个字符开始查找符合 subexpr 正则表达式的子串,如果 return_opt 为 0,返回第 occurrence 次出现的位置,如果 return_opt 为大于 0,则返回该出现位置的下一个字符位置,并符合匹配参数。Subexpr 为匹配的子 pattern。

返回值:如果 str、pattern、position、occurrence、return_opt、match_param 和 subexpr 中任一个为 NULL,则返回 NULL。否则返回符合条件的子串位置,如果没有找到,则返回 0。

SELECT REGEXP_INSTR('a为了aaac','aa') FROM DUAL;

查询结果:4

SELECT REGEXP_INSTR('a为了aaac','aa',5) FROM DUAL;

查询结果:5

SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 2) "REGEXP_INSTR" FROM DUAL;

查询结果:4

  1. REGEXP_SUBSTR
语法:REGEXP_SUBSTR(str, pattern [,position [, occurrence [,match_param[, subexpr]]]])

功能:根据 pattern 正则表达式,从 str 字符串的第 position 个字符开始查找符合 subexpr 正则表达式的子串,返回第 occurrence 次出现的子串,并符合匹配参数 match_param。occurrence 默认为 1。如果 position 或 occurrence 的输入值不为正数,则报错。

返回值:如果 str、pattern、position、occurrence、match_param 和 subexpr 中任一个为 NULL,则返回 NULL。如果找到符合正则表达式的子串,则返回匹配的子串;如果没有找到,则返回 NULL。

SELECT REGEXP_SUBSTR('a为aa了aac','(a*)',2) FROM DUAL;

查询结果:空

SELECT REGEXP_SUBSTR('a为aa了aac','(a+)',2) FROM DUAL;

查询结果:aa

SELECT REGEXP_SUBSTR('500 DM8 DATABASE, SHANG HAI, CN', ',[\^,]+,', 5, 1, 'i', 0) "REGEXPR_SUBSTR" FROM DUAL;

查询结果:, SHANG HAI,

  1. REGEXP_REPLACE
语法:REGEXP_REPLACE(str, pattern [, replace_str [, position [,occurrence [,match_param]]]])

功能:根据 pattern 正则表达式,从 str 字符串的第 position 个字符开始查找符合正则表达式的子串,并用 replace_str 进行替换第 occurrence 次出现的子串,并符合匹配参数 match_param。occurrence 默认为 0,替换所有出现的子串。replace_str 默认为空串,在替换过程中,则相当于删除查找到的子串;position 默认值为 1,如果 position 的值不为正整数,则报错;

返回值:返回替换后的 str。如果 str、pattern、position、occurrence 和 match_param 中任一个为 NULL,则返回 NULL;如果 str 中所有的字符都被空串替换,则返回 NULL,相当于删除所有的字符。

SELECT REGEXP_REPLACE('a为了aaac','aa','bb') FROM DUAL;

查询结果:a 为了 bbac

SELECT REGEXP_REPLACE('a为了ac','aa','bb') FROM DUAL;

查询结果:a 为了 ac

SELECT REGEXP_REPLACE('a为aa了aac','aa','bb') FROM DUAL;

查询结果:a 为 bb 了 bbc

SELECT REGEXP_REPLACE('500 DM8 DATABASE, SHANG HAI, CN', ',[^,]+,', ', WU HAN,', 5, 1,'i') "REGEXPR_REPLACE" FROM DUAL;

查询结果:500 DM8 DATABASE, WU HAN, CN

SELECT REGEXP_REPLACE('www1234xxxx3q', '([[:alpha:]]+)', 'AAA\1') FROM DUAL;

此处使用了正则表达式的反向引用功能,查询结果为:AAAwww1234AAAxxxx3AAAq

  1. 函数 OVERLAY
语法:OVERLAY(char1 PLACING char2 FROM m [ FOR n ])

功能:用串 char2(称为“替换字符串”)覆盖源串 char1 的指定子串,该子串是通过在源串中的给定起始位置的数值(m)和长度的数值(n)而指明,来修改一个串自变量。当子串长度为 0 时,不会从源串中移去任何串;当不指定 n 时,默认 n 为 char2 的长度。函数的返回串是在源串的给定起始位置插入替换字符串所得的结果。

SELECT NAME,OVERLAY(NAME PLACING '口' FROM 3 FOR 2) FROM PRODUCTION.PRODUCT;

查询结果如下表所示。

表8.2.12
NAME "OVERLAY"(NAME,'口',3,2)
红楼梦 红楼口
水浒传 水浒口
老人与海 老人口
射雕英雄传(全四册) 射雕口传(全四册)
鲁迅文集(小说、散文、杂文)全两册 鲁迅口(小说、散文、杂文)全两册
长征 长征口
数据结构(C 语言版)(附光盘) 数据口(C 语言版)(附光盘)
工作中无小事 工作口小事
突破英文基础词汇 突破口基础词汇
噼里啪啦丛书(全 7 册) 噼里口丛书(全 7 册)
SELECT OVERLAY('txxxxas' PLACING 'hom' FROM 2 FOR 4);

查询结果为:thomas

  1. 函数 TEXT_EQUAL
语法:TEXT_EQUAL(n1,n2)

功能:返回 n1,n2 的比较结果,完全相等,返回 1;否则返回 0。n1,n2 的类型为 CLOB、TEXT 或 LONGVARCHAR。如果 n1 或 n2 均为空串或 NULL,结果返回为 1;否则只有一个为空串或为 NULL,结果返回 0。不忽略结果空格和英文字母大小写。

SELECT TEXT_EQUAL('a', 'b');

查询结果为:0

SELECT TEXT_EQUAL('a','a');

查询结果为:1

  1. 函数 BLOB_EQUAL
语法:BLOB_EQUAL(n1,n2)

功能:返回 n1,n2 两个数的比较结果,完全相等,返回 1;否则返回 0。n1,n2 的类型为 BLOB、IMAGE 或 LONGVARBINARY。如果 n1 或 n2 均为空串或 NULL,结果返回为 1;否则只有一个为空串或为 NULL,结果返回 0。

SELECT BLOB_EQUAL(0xFFFEE, 0xEEEFF);

查询结果为:0

SELECT BLOB_EQUAL(0xFFFEE, 0xFFFEE);

查询结果为:1

  1. 函数 NLSSORT
语法:NLSSORT(str1 [,nls_sort=str2])

功能:返回对自然语言排序的编码。当只有 str1 一个参数时,与 RAWTOHEX 类似,返回 16 进制字符串。str2 决定按哪种方式排序:BINARY 表示按默认字符集二进制编码排序;SCHINESE_PINYIN_M 表示按中文拼音排序;SCHINESE_STROKE_M 表示按中文笔画排序;SCHINESE_RADICAL_M 表示按中文部首排序;THAI_CI_AS 表示按泰文排序;KOREAN_M 表示按韩文排序。当 str2 为 BINARY 时,忽略第二个参数,等价于 NLSSORT(str1)。仅字符集为 UTF-8 的数据库支持自然语言按泰文排序。

用户可以通过 ALTER SESSION 语法(具体请参考 3.15.4 自然语言排序方式)设置 nls_sort 的参数值,修改后的参数值只对当前会话起作用,当函数 NLSSORT 只有 str1 一个参数时,当前会话默认使用 ALTER SESSION 设置的 nls_sort 的参数值。

SELECT NLSSORT('abc') FROM DUAL;

查询结果为:61626300

CREATE TABLE TEST(C1 VARCHAR2(200));
INSERT INTO TEST VALUES('啊');
INSERT INTO TEST VALUES('不');
INSERT INTO TEST VALUES('才');
INSERT INTO TEST VALUES('的');
INSERT INTO TEST VALUES('一');
INSERT INTO TEST VALUES('二');
INSERT INTO TEST VALUES('三');
INSERT INTO TEST VALUES('四');
INSERT INTO TEST VALUES('品');
INSERT INTO TEST VALUES('磊');
SELECT * FROM TEST ORDER BY NLSSORT(C1, 'NLS_SORT=SCHINESE_PINYIN_M');   	--拼音

查询结果为:

行号 C1

1 啊

2 不

3 才

4 的

5 二

6 磊

7 品

8 三

9 四

10 一

SELECT * FROM TEST ORDER BY NLSSORT(C1, 'NLS_SORT=SCHINESE_STROKE_M'); --笔画

查询结果为:

行号 C1

1 一

2 二

3 三

4 才

5 不

6 四

7 的

8 品

9 啊

10 磊

SELECT * FROM TEST ORDER BY NLSSORT(C1, 'NLS_SORT=SCHINESE_RADICAL_M'); --部首

查询结果为:

行号 C1

1 一

2 二

3 三

4 不

5 品

6 啊

7 四

8 才

9 的

10 磊

SELECT C1,NLSSORT(C1),NLSSORT(C1, 'NLS_SORT=SCHINESE_PINYIN_M') FROM TEST ORDER BY NLSSORT(C1, 'NLS_SORT=SCHINESE_PINYIN_M');

分别返回 c1,返回将 c1 转化后的 16 进制字符串,返回用来为汉字排序的编码。

查询结果为:

C1 NLSSORT ( C1 ) NLSSORT ( C1, 'nls_sort=schinese_pinyin_m')

啊 B0A100 3B2C

不 B2BB00 4248

才 B2C500 4291

的 B5C400 4D8D

二 B6FE00 531D

磊 C0DA00 743E

品 C6B700 8898

三 C8FD00 932C

四 CBC400 996A

一 D2BB00 B310

SELECT C1,NLSSORT(C1, 'NLS_SORT=BINARY') FROM TEST ORDER BY NLSSORT(C1, 'NLS_SORT=SCHINESE_PINYIN_M');

NLSSORT(C1, 'NLS_SORT=BINARY')等价于 NLSSORT(C1)。

查询结果为:

C1 NLSSORT(C1,'NLS_SORT=BINARY')

啊 B0A100

不 B2BB00

才 B2C500

的 B5C400

二 B6FE00

磊 C0DA00

品 C6B700

三 C8FD00

四 CBC400

一 D2BB00

ALTER SESSION SET NLS_SORT='SCHINESE_PINYIN_M';

SELECT C1,NLSSORT(C1) FROM TEST ORDER BY NLSSORT(C1);

查询结果为:

C1 NLSSORT(C1)

啊 3B2C

不 4248

才 4291

的 4D8D

二 531D

磊 743E

品 8898

三 932C

四 996A

一 B310

SELECT C1,NLSSORT(C1, 'NLS_SORT=SCHINESE_PINYIN_M') FROM TEST ORDER BY NLSSORT(C1, 'NLS_SORT=SCHINESE_PINYIN_M');

查询结果为:

C1 NLSSORT(C1,'NLS_SORT=SCHINESE_PINYIN_M')

啊 3B2C

不 4248

才 4291

的 4D8D

二 531D

磊 743E

品 8898

三 932C

四 996A

一 B310

可以看出,上述两个 SQL 语句的查询结果一致。由于使用 ALTER SESSION 语法设置 nls_sort 的参数值为 schinese_pinyin_m,因此在当前会话中,当函数 NLSSORT 只有一个参数时,默认第二个参数 nls_sort 的值为 schinese_pinyin_m。

  1. 函数 GREATEST
语法:GREATEST(char {,char})

功能:求一个或多个字符串中最大的字符串。

SELECT GREATEST('abb','abd', 'abc');

查询结果:abd

  1. 函数 GREAT
语法:GREAT (char1, char2)

功能:求 char1、char2 中最大的字符串。

SELECT GREAT ('abb','abd');

查询结果:abd

  1. 函数 TO_SINGLE_BYTE
语法: TO_SINGLE_BYTE(
STR IN VARCHAR
) return RVAL VARCHAR;

功能:将多字节形式的字符(串)转换为对应的单字节形式

参数: STR:待转换的字符,长度为 4096

返回值: RVAL:函数返回值,长度为 8187

例:

SELECT LENGTHB(TO_SINGLE_BYTE('aa'));

返回结果:2

  1. 函数 TO_MULTI_BYTE
语法: to_multi_byte(
STR IN VARCHAR
) return RVAL VARCHAR;

功能:将单字节形式的字符(串)转换为对应的多字节形式 (不同的字符集转换结果不同)

参数:STR :待转换的字符,长度为 4096

返回值:RVAL:函数返回值,长度为 8187

SELECT LENGTHB(TO_MULTI_BYTE('aa'));

返回结果:4

  1. 函数 EMPTY_BLOB
语法:EMPTY_BLOB return RVAL blob;

功能:初始化 blob 字段

返回值:RVAL:长度为 0 的 blob 数据

DROP TABLE TT;
CREATE TABLE TT(C1 BLOB, C2 INT);
INSERT INTO TT VALUES(EMPTY_BLOB(),1);
INSERT INTO TT VALUES(NULL,2);
INSERT INTO TT VALUES(0X123,3);
SELECT LENGTHB(C1) FROM TT;

返回值:

LENGTHB(C1)
-----------
0
NULL
2
  1. 函数 EMPTY_CLOB
语法:EMPTY_CLOB return RVAL clob;

功能:初始化 clob 字段

返回值: RVAL:长度为 0 的 clob 数据

DROP TABLE TT;
CREATE TABLE TT(C1 CLOB, C2 INT);
INSERT INTO TT VALUES(EMPTY_CLOB(),1);
INSERT INTO TT VALUES(NULL,2);
INSERT INTO TT VALUES('0X123',3);
SELECT LENGTHB(C1) FROM TT; 

返回值:

LENGTHB(C1)
-----------
0
NULL
5
  1. 函数 UNISTR
语法:UNISTR (char)

功能:将字符串 char 中,ascii 码(‘\XXXX’4 个 16 进制字符格式)转成本地字符。对于其他字符保持不变。

在 GB18030 库下,执行如下操作:

SELECT UNISTR('\803F\55B5\55B5kind又\006e\0069\0063\0065') FROM DUAL;

查询结果为: 耿喵喵 kind 又 nice

  1. 函数 ISNULL
语法:ISNULL(char)

功能:判断表达式是否为 NULL,为 NULL 返回 1,否则返回 0。

查询总经理的 MANAGERID 是否为空:

SELECT ISNULL(MANAGERID) FROM RESOURCES.EMPLOYEE WHERE TITLE='总经理';

58. 函数 CONCAT_WS

语法:CONCAT_WS(delim, char1,char2,char3,…)

功能:顺序联结多个字符串成为一个字符串,并用 delim 分割。

如果 delim 取值为 NULL,则返回 NULL。如果其它参数为 NULL,在执行拼接过程中跳过取值为 NULL 的参数。

SELECT CONCAT_WS(',,','11','22','33');

返回值:11,,22,,33

59. 函数 SUBSTRING_INDEX

语法:substring_index (char, delim, count)

功能:按关键字截取字符串,截取到指定分隔符出现指定次数位置之前。

char 为被截取的字符串,delim 为关键字符串,count 为关键字出现的次数。如果 count 为负,则从后往前截取,截取到指定分隔符出现指定次数位置之后。

SELECT SUBSTRING_INDEX('blog.jb51.net', '.',2);

返回值:blog.jb51

SELECT SUBSTRING_INDEX('blog.jb51.net', '.',-2);

返回值:jb51.net

8.3 日期时间函数

日期时间函数的参数至少有一个是日期时间类型(TIME,DATE,TIMESTAMP),返回值一般为日期时间类型和数值类型。对于日期时间类型数据的取值范围,请参考 1.4.3 日期时间数据类型,若日期时间类型的参数或返回值超过限制范围,则报错。

由于 DM 支持儒略历,并考虑了历史上从儒略历转换至格里高利日期时的异常,不计算'1582-10-05'到'1582-10-14'之间的 10 天,因此日期时间函数也不计算这 10 天。

  1. 函数 ADD_DAYS

语法:ADD_DAYS( date, n)

功能:返回日期 date 加上相应天数 n 后的日期值。n 可以是任意整数,date 是日期类型(DATE)或时间戳类型(TIMESTAMP),返回值为日期类型(DATE)。

SELECT ADD_DAYS( DATE '2000-01-12',1);

查询结果为:2000-01-13

  1. 函数 ADD_MONTHS
语法:ADD_MONTHS(date,n)

功能:返回日期 date 加上 n 个月的日期时间值。n 可以是任意整数,date 是日期类型(DATE)或时间戳类型(TIMESTAMP),返回类型固定为日期类型(DATE)。如果相加之后的结果日期中月份所包含的天数比 date 日期中的日分量要少,那么结果日期的该月最后一天被返回。

SELECT ADD_MONTHS(DATE '2000-01-31',1);

查询结果为:2000-02-29

SELECT ADD_MONTHS(TIMESTAMP '2000-01-31 20:00:00',1);

查询结果为:2000-02-29

3.函数 ADD_WEEKS

语法:ADD_WEEKS( date, n)

功能:返回日期 date 加上相应星期数 n 后的日期值。n 可以是任意整数,date 是日期类型(DATE)或时间戳类型(TIMESTAMP),返回类型固定为日期类型(DATE)。

SELECT ADD_WEEKS( DATE '2000-01-12',1);

查询结果为: 2000-01-19

  1. 函数 CURDATE
语法:CURDATE()

功能:返回当前日期值,结果类型为 DATE。

SELECT CURDATE();

查询结果为:执行此查询当天日期,如 2003-02-27

  1. 函数 CURTIME
语法:CURTIME(n)

功能:返回当前时间值,结果类型为 TIME WITH TIME ZONE。

参数:n:指定毫秒的精度。取值范围 0-6,默认为 6。

SELECT CURTIME();

查询结果为:执行此查询的当前时间,如 14:53:54.859000 +8:00

6.函数 CURRENT_DATE

语法:CURRENT_DATE

功能:返回当前日期值,结果类型为 DATE,等价于 CURDATE()。

  1. 函数 CURRENT_TIME
语法:CURRENT_TIME(n)

功能:返回当前时间值,结果类型为 TIME WITH TIME ZONE,等价于 CURTIME()。

参数:n:指定毫秒的精度。取值范围 0-6,默认为 6。

  1. 函数 CURRENT_TIMESTAMP
语法:CURRENT_TIMESTAMP(n)

功能:返回当前带会话时区的时间戳,结果类型为 TIMESTAMP WITH TIME ZONE。

参数:n:指定毫秒的精度。取值范围 0-6,默认为 6。

SELECT CURRENT_TIMESTAMP();

查询结果为:执行此查询的当前日期时间,如 2011-12-27 13:03:56.000000 +8:00

  1. 函数 DATEADD
语法:DATEADD(datepart,n,date)

功能:向指定的日期 date 加上 n 个 datepart 指定的时间段,返回新的 timestamp 值。datepart 取值见下表。

表8.3.1 datepart取值
datepart 取值 datepart 意义
YEAR、YYYY、YY、SQL_TSI_YEAR
MONTH、MM、M、SQL_TSI_MONTH
DAY、DD、D、SQL_TSI_DAY
HOUR、HH、SQL_TSI_HOUR
MINUTE、MI、N、SQL_TSI_MINUTE
SECOND、S、SQL_TSI_SECOND
MILLISECOND、MS、SQL_TSI_FRAC_SECOND 毫秒
QUARTER、QQ、Q、SQL_TSI_QUARTER 所处的季度
DAYOFYEAR、DY、Y 在年份中所处的天数
WEEK、WK、WW、SQL_TSI_WEEK 在年份中所处的周数
WEEKDAY、DW 在一周中所处的天数
  1. 函数 DATEDIFF/BIGDATEDIFF

语法:DATEDIFF(datepart,date1,date2)

功能:返回跨两个指定日期的日期和时间边界数。datepart 取值见表 8.3.1。

注:当结果超出整数值范围,DATEDIFF 产生错误。对于毫秒 MILLISECOND,最大数是 24 天 20 小时 31 分钟零 23.647 秒。对于秒,最大数是 68
年。若想提高可以表示的范围,可以使用 BIGDATEDIFF,其使用方法与 DATEDIFF 函数一致,只是可以表示更广范围的秒和毫秒。

SELECT DATEDIFF(QQ, '2003-06-01', DATE '2002-01-01');

查询结果为:-5

SELECT DATEDIFF(MONTH, '2001-06-01', DATE '2002-01-01');

查询结果为:7

SELECT DATEDIFF(WK, DATE '2003-02-07',DATE '2003-02-14');

查询结果为:1

SELECT DATEDIFF(MS,'2003-02-14 12:10:10.000','2003-02-14 12:09:09.300');

查询结果为:-60700

  1. 函数 DATEPART/date_part
语法:DATEPART(datepart,date)

功能:返回代表日期 date 的指定部分的整数。datepart 取值请参 DATEDIFF(datepart,date1,date2)的参数。

SELECT DATEPART(SECOND, DATETIME '2000-02-02 13:33:40.00');

查询结果为:40

SELECT DATEPART(DY, '2000-02-02');

查询结果为:33

SELECT DATEPART(WEEKDAY, '2002-02-02');

查询结果为:7

说明:日期函数:date_part,其功能与 datepart 完全一样。但是写法有点不同:select datepart(year,'2008-10-10');如果用 date_part,则要写成:select date_part('2008-10-10','year'),即:参数顺序颠倒,同时指定要获取的日期部分的参数要带引号。

  1. 函数 DAY
语法:DAY(date)

功能:返回指定日期在月份中的天数

SELECT DAY('2016-06-07');

查询结果为:7

  1. 函数 DAYNAME
语法:DAYNAME(date)

功能:返回日期的星期名称。

SELECT DAYNAME(DATE '2012-01-01');

查询结果为:Sunday

  1. 函数 DAYOFMONTH
语法:DAYOFMONTH(date)

功能:返回日期为所处月份中的第几天。

SELECT DAYOFMONTH('2003-01-03');

查询结果为:3

  1. 函数 DAYOFWEEK
语法:DAYOFWEEK(date)

功能:返回日期为所处星期中的第几天。

SELECT DAYOFWEEK('2003-01-01');

查询结果为:4

  1. 函数 DAYOFYEAR
语法:DAYOFYEAR(date)

功能:返回日期为所处年中的第几天。

SELECT DAYOFYEAR('2003-03-03');

查询结果为:62

  1. 函数 DAYS_BETWEEN
语法: DAYS_BETWEEN(dt1,dt2)

功能:返回两个日期之间相差的天数。

  1. 函数 EXTRACT
语法:EXTRACT(dtfield FROM date)

功能:EXTRACT 从日期时间类型或时间间隔类型的参数 date 中抽取 dtfield 对应的数值,并返回一个数字值。如果 date 是 NULL,则返回 NULL。Dtfiled 可以是 YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。对于 SECOND 之外的任何域,函数返回整数,对于 SECOND 返回小数。

例:

SELECT EXTRACT(YEAR FROM DATE '2000-01-01');

查询结果为:2000

SELECT EXTRACT(DAY FROM DATE '2000-01-01');

查询结果为:1

SELECT EXTRACT(MINUTE FROM TIME '12:00:01.35');

查询结果为:0

SELECT EXTRACT(TIMEZONE_HOUR FROM TIME '12:00:01.35 +9:30');

查询结果为:9

SELECT EXTRACT(TIMEZONE_MINUTE FROM TIME '12:00:01.35 +9:30');

查询结果为:30

SELECT EXTRACT(SECOND FROM TIMESTAMP '2000-01-01 12:00:01.35');

查询结果为:1.3500000000E+000

SELECT EXTRACT(SECOND FROM INTERVAL '-05:01:22.01' HOUR TO SECOND);

查询结果为:-2.2010000000E+001

  1. 函数 GETDATE
语法:GETDATE(n)

功能:返回系统的当前时间戳。

参数:n:指定毫秒的精度。取值范围 0-6,默认为 6。

SELECT GETDATE();

查询结果为:返回系统的当前日期时间,如 2011-12-05 11:31:10.359000

  1. 函数 GREATEST
语法:GREATEST(date {,date})

功能:求一个或多个日期中的最大日期。

SELECT GREATEST(date'1999-01-01',date'1998-01-01',date'2000-01-01');

查询结果:2000-01-01

  1. 函数 GREAT
语法:GREAT (date1,date2)

功能:求 date1、date2 中的最大日期。

SELECT GREAT (date'1999-01-01', date'2000-01-01');

查询结果:2000-01-01

  1. 函数 HOUR
语法:HOUR(time)

功能:返回时间中的小时分量。

SELECT HOUR(TIME '20:10:16');

查询结果为:20

  1. 函数 LAST_DAY
语法:LAST_DAY(date)

功能:返回 date 所在月最后一天的日期,date 是日期类型(DATE)或时间戳类型(TIMESTAMP),返回类型与 date 相同。

SELECT LAST_DAY(SYSDATE) "Days Left";

查询结果为:如:当前日期为 2003 年 2 月的某一天,则结果为 2003-02-28

SELECT LAST_DAY(TIMESTAMP '2000-01-11 12:00:00');

查询结果为:2000-01-31

  1. 函数 LEAST
语法:LEAST(date {,date})

功能:求一个或多个日期中的最小日期。

SELECT LEAST(date'1999-01-01',date'1998-01-01',date'2000-01-01');

查询结果:1998-01-01

  1. 函数 MINUTE
语法:MINUTE(time)

功能:返回时间中的分钟分量。

SELECT MINUTE('20:10:16');

查询结果为:10

  1. 函数 MONTH
语法:MONTH(date)

功能:返回日期中的月份分量。

SELECT MONTH('2002-11-12');

查询结果为:11

  1. 函数 MONTHNAME
语法:MONTHNAME(date)

功能:返回日期中月份分量的名称。

SELECT MONTHNAME('2002-11-12');

查询结果为:November

  1. 函数 MONTHS_BETWEEN
语法:MONTHS_BETWEEN(date1,date2)

功能:返回 date1 和 date2 之间的月份值。如果 date1 比 date2 晚,返回正值,否则返回负值。如果 date1 和 date2 这两个日期为同一天,或者都是所在月的最后一天,则返回整数,否则返回值带有小数。date1 和 date2 是日期类型(DATE)或时间戳类型(TIMESTAMP)。

SELECT MONTHS_BETWEEN(DATE '1995-02-28', DATE '1995-01-31') "Months";

查询结果为:1.0

SELECT MONTHS_BETWEEN(TIMESTAMP '1995-03-28 12:00:00', TIMESTAMP '1995-01-31 12:00:00') "Months";

查询结果为:1.903226

  1. 函数 NEXT_DAY
语法:NEXT_DAY(date,char)

功能:返回在日期 date 之后满足由 char 给出的条件的第一天。char 指定了一周中的某一个天(星期几),返回值的时间分量与 date 相同,char 是大小写无关的。

Char 取值如表 8.3.8 所示。

表8.3.2 星期描述说明
输入值 含义
SUN 星期日
SUNDAY
MON 星期一
MONDAY
TUES 星期二
TUESDAY
WED 星期三
WEDNESDAY
THURS 星期四
THURSDAY
FRI 星期五
FRIDAY
SAT 星期六
SATURDAY

SELECT NEXT_DAY(DATE '2001-08-02', 'MONDAY');

查询结果为:2001-08-06

SELECT NEXT_DAY('2001-08-02 12:00:00', 'FRI');

查询结果为:2001-08-03

  1. 函数 NOW
语法:NOW(n)

功能:返回系统的当前时间戳。等价于 GETDATE()。

参数:n:指定毫秒的精度。取值范围 0-6,默认为 6。

  1. 函数 QUARTER
语法:QUARTER(date)

功能:返回日期在所处年中的季度数。

SELECT QUARTER('2002-08-01');

查询结果为:3

  1. 函数 SECOND
语法:SECOND(time)

功能:返回时间中的秒分量。

SELECT SECOND('08:10:25.300');

查询结果为:25

  1. 函数 ROUND
语法:ROUND(date[, fmt])

功能:将日期时间 date 四舍五入到最接近格式参数 fmt 指定的形式。如果没有指定语法的话,到今天正午 12P.M.为止的时间舍取为今天的日期,之后的时间舍取为第二天 12A.M.。日期时间 12A.M.,为一天的初始时刻。参数 date 的类型可以是 DATE 或 TIMESTAMP,但应与 fmt 相匹配。函数的返回结果的类型与参数 date 相同。fmt 具体如表 8.3.8 所示。

表8.3.3 日期时间说明
fmt 的格式 含义 date 数据类型
cc, scc 世纪,从 1950、2050 等年份的一月一号午夜凌晨起的日期,舍取至下个世纪的一月一号 DATE TIMESTAMP
syear, syyy, y, yy, yyy, yyyy, year 年,从七月一号午夜凌晨起的日期,舍取至下个年度的一月一号 DATE TIMESTAMP
Q 季度,从十六号午夜凌晨舍取到季度的第二个月,忽略月中的天数 DATE TIMESTAMP
month,mon, mm, m, rm 月,从十六号午夜凌晨舍取 DATE TIMESTAMP
Ww 舍取为与本年第一天星期数相同的最近的那一天 DATE TIMESTAMP
W 舍取为与本月第一天星期数相同的最近的一天 DATE TIMESTAMP
iw 舍取为最近的周一 DATE TIMESTAMP
ddd, dd, j 从正午起,舍取为下一天,默认值 DATE TIMESTAMP
day, dy, d 星期三正午起,舍取为下个星期天 DATE TIMESTAMP
hh, hh12, hh24 在一个小时的 30 分 30 秒之后的时间舍取为下一小时 TIME TIMESTAMP
Mi 在一个分钟 30 秒之后的时间舍取为下一分 TIME TIMESTAMP

有关 ww 和 w 的计算进一步解释如下(下面的时间仅当 date 参数为时间戳时才有效):

ww 产生与本年第一天星期数相同的最近的日期。因为每两个星期数相同日期之间相隔六天,这意味着舍取结果在给定日期之后三天以内。例如,如果本年第一天为星期二,若给定日期在星期五午夜 23:59:59 之前(包含星期五 23:59:59),则舍取为本星期的星期二的日期;否则舍取为下星期的星期二的日期。

w 计算的方式类似,不是产生最近的星期一 00:00:00,而是产生与本月第一天相同的星期数的日期。

SELECT ROUND(DATE '1992-10-27', 'scc');

查询结果为:2001-01-01

SELECT ROUND(DATE '1992-10-27', 'YEAR') "FIRST OF THE YEAR";

查询结果为:1993-01-01

SELECT ROUND(DATE '1992-10-27', 'q');

查询结果为:1992-10-01

SELECT ROUND(DATE '1992-10-27', 'month');

查询结果为:1992-11-01

SELECT ROUND(TIMESTAMP '1992-10-27 11:00:00', 'ww');

查询结果为:1992-10-28 00:00:00.000000

SELECT ROUND(TIMESTAMP '1992-10-27 11:00:00', 'w');

查询结果为:1992-10-29 00:00:00.000000

SELECT ROUND(TIMESTAMP '1992-10-27 12:00:01', 'ddd');

查询结果为:1992-10-28 00:00:00.000000

SELECT ROUND(DATE '1992-10-27', 'day');

查询结果为:1992-10-25

SELECT ROUND(TIMESTAMP '1992-10-27 12:00:31', 'hh');

查询结果为:1992-10-27 12:00:00.000000

SELECT ROUND(TIMESTAMP '1992-10-27 12:00:31', 'mi');

查询结果为:1992-10-27 12:01:00.000000

  1. 函数 TIMESTAMPADD
语法:TIMESTAMPADD(datepart,n,timestamp)

功能:返回时间戳 timestamp 加上 n 个 datepart 指定的时间段的结果,datepart 取值见表 8.3.1。

SELECT TIMESTAMPADD(SQL_TSI_FRAC_SECOND, 5, '2003-02-10 08:12:20.300' );

查询结果为:2003-02-10 08:12:20.305000

SELECT TIMESTAMPADD(SQL_TSI_YEAR, 30, DATE '2002-01-01');

查询结果为:2032-01-01 00:00:00.000000

SELECT TIMESTAMPADD(SQL_TSI_QUARTER, 2, TIMESTAMP '2002-01-01 12:00:00');

查询结果为:2002-07-01 12:00:00.000000

SELECT TIMESTAMPADD(SQL_TSI_DAY, 40, '2002-12-01 12:00:00');

查询结果为:2003-01-10 12:00:00.000000

SELECT TIMESTAMPADD(SQL_TSI_WEEK, 1, '2002-01-30');

查询结果为:2002-02-06 00:00:00.000000

  1. 函数 TIMESTAMPDIFF
语法:TIMESTAMPDIFF(datepart,timestamp1,timestamp2)

功能:返回一个表明 timestamp2 与 timestamp1 之间的指定 datepart 类型的时间间隔的整数,datepart 取值见表 8.3.1。

注:当结果超出整数值范围,TIMESTAMPDIFF 产生错误。对于秒级 SQL_TSI_SECOND,最大数是 68 年。

SELECT TIMESTAMPDIFF(SQL_TSI_FRAC_SECOND,
'2003-02-14 12:10:10.000', '2003-02-14 12:09:09.300');

查询结果为:-60700

SELECT TIMESTAMPDIFF(SQL_TSI_QUARTER, '2003-06-01', DATE '2002-01-01');

查询结果为:-5

SELECT TIMESTAMPDIFF(SQL_TSI_MONTH, '2001-06-01', DATE '2002-01-01');

查询结果为:7

SELECT TIMESTAMPDIFF(SQL_TSI_WEEK, DATE '2003-02-07',DATE '2003-02-14');

查询结果为:1

  1. 函数 SYSDATE
语法: SYSDATE()

功能: 获取系统当前时间。

SELECT SYSDATE();

查询结果:当前系统时间

  1. 函数 TO_DATE/TO_TIMESTAMP/TO_TIMESTAMP_TZ
语法:TO_DATE(char [,fmt[,'nls']]) 或TO_TIMESTAMP(char [,fmt[,'nls']]) 或TO_TIMESTAMP_TZ(char [,fmt])

功能:将 CHAR 或者 VARCHAR 类型的值转换为 DATE/TIMESTAMP 数据类型。TO_DATE 的结果不带毫秒精度,TO_TIMESTAMP 的结果带 6 位毫秒精度。TO_TIMESTAMP_TZ 的结果带上服务器的时区。

NLS:指定日期时间串的语言类型:AMERICAN、ENGLISH 和 SIMPLIFIED CHINESE,分别表示美语,英语和简体中文。其中,AMERICAN 和 ENGLISH 的效果相同。缺省为 SIMPLIFIED CHINESE。 这个参数的使用形式是:“NLS_DATE_LANGUAGE=''语言类型''”。

FMT:指定日期语法格式,需按照特定的格式书写。FMT 内容有三种选择:DATE、DATE+TIME 或 TIME。合法的 DATE 格式为年月日、月日年或日月年,各部分之间可以有分隔符或者没有分隔符,DATE 的分隔符下文有详细介绍;合法的 TIME 格式为:时分或时分秒,TIME 分隔符只能为":"。例如'YYYY/MM/DD'、'YYYYMMDD HH24:MI:SS'、'HH24:MI',其中 YYYYMMDD、HH24MISS 为格式符;/:为分割符。

DM 缺省的日期语法格式 FMT 为:'YYYY-MM-DD HH:MI:SS.FF6'。

SQL> SELECT TO_date(' 20200215 14.47.38','YYYY-MM-DD HH24:MI:SS');

查询结果:2020-02-15 14:47:38

SQL> SELECT TO_date(' 20200215 14.47.38','YYYY-MM-DD HH24:MI:SS','NLS_DATE_LANGUAGE=''AMERICAN''');

查询结果:2020-02-15 14:47:38

日期语法格式 FMT 中的格式符、分隔符和 FX 固定格式器。详细介绍如下:

  • 格式符

日期语法格式 FMT 中的格式符由年、月、日、时、分、秒等元素组成。详细的元素介绍,参见表 8.3.4。

表8.3.4 格式符
元素 说明 区别
D 周中的某一天,星期天算起
DD 月中的某一天
DDD 年中的某一天
HH HH12 HH24 天中的时(0-23)。 HH,HH12 为 12 小时制。HH24 为 24 小时制
MI 分(0-59)
MM 月(01-12)
SS 秒(0-59)
SSSSS 一天从午夜开始的累积秒数(0-86399)
TZH 时区中的小时,例如 hh:mi:ss.fftzh:tzh'
TZM 时区中的分钟
FF[1…9] 毫秒,[1…9]指定毫秒的精度,不指定时缺省为 9
SSXFF X 表示秒和毫秒的间隔,等价于.
YYYY 4 位的年份
YY 年份的最后 2 位数字
Y 年份的最后 1 位数字
AD/A.D. 公元,不能为 0
AM/A.M. 上午
BC/B.C. 公元前
CC/SCC 世纪 不适用于 TO_DATE 中
DAY 星期(如星期五或 FRIDAY)
DL 返回长日期格式,包括年月日和星期几
DS 返回短日期格式,包括年月日
DY 星期的缩写形式(如星期五或 FRI)
IW 星期数(当前日期所在星期是这一年的第几个星期,基于 ISO 标准) 不适用于 TO_DATE 中
MON 月份名称的缩写形式(如 12 月或 DEC)
MONTH 月份名称(如 12 月或 DECEMBER)
PM/P.M. 下午
Q 季度号(1、2、3、4) 不适用于 TO_DATE 中
RR/RRRR RR:输入参数年份的 2 位数字和数据库服务器上当前年的后 2 位数字(当年)共同确定 当指定的两位年份数字在 00~49 之间时:若当前年的后两位数字在 00~49 之间,则返回年份的前两位数字和当前年的前两位数字相同;若当前年的后两位数字在 50~99 之间,则返回年份的前两位数字为当前年的前两位数字加 1 当指定的两位年份数字在 50~99 之间时:若当前年份的后两位数字在 00~49 之间,则返回年份的前两位数字为当前年的前两位数字减 1;若当前年的后两位数字在 50~99 之间,则返回年份的前两位数字和当前年的前两位数字相同。 只有后面无其他分隔符且有其它格式符的情况才最多处理两位数字的年份。如:rrmm RRRR:如果输入参数只有两位,则同 RR,否则同 YYYY 作用
WW 星期数(当前日期所在星期是这一年的第几个星期,第一个星期从 1 月 1 日开始,到 1 月 7 日结束)
W 星期数(当前日期所在星期是这个月的第几个星期)
Y,YYY 带逗号的年份,ISO 标准年份。
IYYY,IYY,IY,I 最后倒数 4 位,3 位,2 位,1 位 ISO 标准年份。 ISO 标准认为日期是从周一到周日,按周计算。普通的标准则指定任何一年的一月一号都是周一 不适用于 TO_DATE 中
YYYY/SYYYY ISO 标准年份,S 前缀表示公元前 BC
YEAR/SYEAR 拼写出的年份(比如 TWENTY FIFTEEN)S 前缀表示负年 不适用于 TO_DATE 中
  • DATE 分隔符

下面介绍 DATE 格式中用到的分隔符。分隔符分为两种:一是非限定分隔符,通常指除大小写字母、数字以及双引号之外的所有单字节字符且可打印的。例如:空格、回车键、tab 键、- / , . : *等标点符号。单个双引号 “可以作为原串的分隔符,但是不能在 FMT 中作分割符。二是限定分隔符,指由双引号括起来的任意长度串,比如中文。例如“年”“月”“日”里的年、月、日。

to_date/TO_TIMESTAMP/TO_TIMESTAMP_TZ 函数目前支持的分隔符的规则如下:

  1. 分隔符均包括尾空格,把尾空格全部当作分隔符,但不包括头空格,原串中对应分隔符处忽略头空格。分隔符没有尾空格,原串多出来的后面空格忽略。tab 键与回车键也是如此。
SQL>  select to_date('2001:  10:10','yyyy:mm:dd') from dual;
行号       TO_DATE('2001:10:10','yyyy:mm:dd')
--------------------------------------------------------------
1          2001-10-10 00:00:00
SQL> select to_date('2001--10:10','yyyy  mm:dd') from dual;
行号       TO_DATE('2001--10:10','yyyymm:dd')
--------------------------------------------------------------
1          2001-10-10 0:00:00
  1. 原串中对应位置非限定分隔符的个数小于等于 FMT 中对应位置非限定分隔符的个数。

如果 FMT 设置了某个位置有连续 n 个(n>=1)非限定分隔符,非限定分隔符去除头空格后长度为 m(m>=1),则原串对应位置的非限定分隔符要小于等于 m 个。

例如,FMT 中第二个分隔符为 3 个连续的:,那么原串对应位置的分隔符要小于等于 3 个。

SQL> select to_date('2001-10--10','yyyy:mm:::dd') from dual;
行号       TO_DATE('2001-10--10','yyyy:mm:::dd')
--------------------------------------------------------
1          2001-10-10 00:00:00
  1. 原串中对应位置限定分隔符的个数必须等于 FMT 中实际限定分隔符的个数。

如果 FMT 设置了连续 n 个(n>=1)限定分隔符,限定分隔符去除头空格后长度为 m(m>=1),则源串对应位置必须有 m 个限定分隔符。

例如,FMT 中指定了“猪年”或“猪年”作为实际限定分隔符,那么原串中也要指定相同的“猪年”。

SQL> select to_date('2019猪年10月10日','yyyy"猪年"mm"月"dd"日"') from dual;
行号       TO_DATE('2019猪年10月10日','yyyy"猪年"mm"月"dd"日"')
--------------------------------------------------------------
1          2019-10-10 00:00:00
或
SQL> select to_date('2019猪年10月10日','yyyy"  猪年"mm"月"dd"日"') from dual;
行号       TO_DATE('2019猪年10月10日','yyyy"猪年"mm"月"dd"日"')
--------------------------------------------------------------
1          2019-10-10 00:00:00
  1. 原串中相应位置(限定 + 非限定)分隔符的个数要等于 FMT 中相应位置限定分隔符的个数。

如果 FMT 某个位置设置了连续 n 个(n 大于等于 1)非限定分隔符 + 限定分割符,限定分隔符去除头空格后长度为 m(m 大于等于 1),则源串对应位置必须有 m 个非限定分隔符。(限定 + 非限定)分隔符组合中,非限定符不允许改变。

例如,-3 月和"-mm"月,-29 日和-dd"日"个数,限定符都需满足严格匹配。

SQL> select to_date('2019年-3月-29日','yyyy"年"-mm"月"-dd"日"') from dual;
行号       TO_DATE('2019年-3月-29日','yyyy"年"-mm"月"-dd"日"')
------------------------------------------------------------
1          2019-03-29 00:00:00
  1. 如果 FMT 中只包含非限定分隔符,则原串中对应位置可以有与分隔符内容不相同的分隔符匹配。

如果 FMT 中只包含限定分隔符,则源串中对应位置必须有与实际分隔符内容相同的串匹配。

如果 FMT 中既包含限定分隔符,又包含非限定分隔符(不分顺序),则原串中对应位置必须有与实际分隔符内容相同的串匹配。

SQL> select to_date('2001:1010','yyyy-mmdd') from dual;
行号       TO_DATE('2001:1010','yyyy-mmdd')
---------- -----------------------------------------------
1          2001-10-10 00:00:00   
  1. 如果 FMT 未设置分隔符,则源串对应位置不能有除空格外的分隔符,如果 FMT 中只有空格,则源串对应位置可以有空格,也可以没有。
SQL> select to_date('200112   10','yyyy  mmdd') from dual;
行号       TO_DATE('20011210','yyyymmdd')
----------------------------------------------------
1          2001-12-10 00:00:00
SQL> select to_date('200112     10','yyyymmdd') from dual;
行号       TO_DATE('20011210','yyyymmdd')
------------------------------------------------------
1          2001-12-10 00:00:00
  1. 对于 TO_DATE/TO_TIMESTAMP/TO_TIMESTAMP_TZ 来说,如果 FMT 格式符 XFF 前同时出现非限定分隔符.,不论有多少个.,分隔符.都会被忽略,只都相当于一个 XFF。

例如,在 TO_TIMESTAMP 中...XFF 相当于 XFF。

SQL> SELECT TO_TIMESTAMP ('10秒.123000', 'SS"秒"...XFF') FROM DUAL;
行号       TO_TIMESTAMP('10秒.123000','SS"秒"...XFF')
-------------------------------------------------------------
1          2019-01-01 00:00:10.123000
SQL> SELECT TO_TIMESTAMP ('10.123000', 'SS.XFF') FROM DUAL;
行号       TO_TIMESTAMP('10.123000','SS.XFF')
-------------------------------------------------------------
2019-01-01 00:00:10.123000
  1. 源串中的数据不能多于 FMT 中格式符指定的位数。结尾可以少于 FMT 对应的位数。
  • FX 固定格式器

FX 是 FMT 固定格式全局修改器。使用了 FX 之后,要求源串对应位置的内容必须和 FMT 中 FX 之后的格式严格匹配。FX 可以出现在任何分隔符可以出现的位置。

FX 专门应用于含有限定分隔符的或 fx 标记的 FMT 中。只有全是非限定分隔符的 FMT 或者属于快速格式的 FMT 中,FX 不起作用。快速格式的 FMT 共 12 种,分别为:YYYY-MM-DD(YYYY/MM/DD)、YYYY-DD-MM (YYYY/DD/MM)、MM-DD-YYYY (MM/DD/YYYY)、MM-YYYY-DD(MM/YYYY/DD)、DD-MM-YYYY (DD/MM/YYYY)、DD-YYYY-MM(DD/YYYY/MM)、HH:MI:SS、SS:MI:HH、YYYY-MM-DD HH:MI:SS (YYYY/MM/DDHH:MI:SS)、YYYY-MM-DD HH:MI:SS.ff[n] (YYYY/MM/DD HH:MI:SS[n])、YYYYMMDD、YYYYMMDD HH:MI:SS。

无 FX 情况下,源串格式和 FMT 不需要完全匹配(非限定分隔符个数少于等于源串、非限定分隔符内容不同,固定格式位数不一样等模糊匹配),也能执行成功。

SQL> SELECT TO_DATE('19年08月01','yyyy"年"mm"月"dd') FROM DUAL;
行号       TO_DATE('19年08月01','yyyy"年"mm"月"dd')
----------------------------------------------------------------------------
1          19-08-01 00:00:00

有 FX 情况下,源串格式和 FMT 没有完全匹配,报错:文字与格式字符串不匹配。

SQL> SELECT TO_DATE('19年08月01','fxyyyy"年"mm"月"dd') FROM DUAL;
SELECT TO_DATE('19年08月01', 'fxyyyy"年"mm"月"dd') FROM DUAL;
[-6130]:文字与格式字符串不匹配.

有 FX 情况下,FX 位于 FMT 最前端,此时源串格式需要和 FMT 完全匹配,才能执行成功。

SQL> SELECT TO_DATE('2019年08月01','fxyyyy"年"mm"月"dd') FROM DUAL;
行号       TO_DATE('2019年08月01','fxyyyy"年"mm"月"dd')
----------------------------------------------------------------------------
1          2019-08-01 00:00:00
  1. 函数 FROM_TZ
语法:FROM_TZ(timestamp,timezone|tz_name])

功能:将时间戳类型 timestamp 和时区类型 timezone(或时区名称 tz_name)转化为 timestamp
with timezone 类型 。

timestamp 缺省的日期语法为:"YYYYMMDD HH:MI:SS"或者"YYYYMMDD "。

时区设置范围为:-12:59~+14:00。

时区名:ASIA/HONG_KONG(即 +08:00)。

使用时区:

SELECT FROM_TZ(TO_TIMESTAMP('20091101 09:10:21','YYYYMMDD HH:MI:SS '),'+09:00');

查询结果:2009-11-01 09:10:21.000000 +09:00

使用时区名:

SELECT FROM_TZ(TO_TIMESTAMP('20091101','YYYYMMDD'),'ASIA/HONG_KONG') ;

查询结果:2009-11-01 00:00:00.000000 +08:00

不指定格式:

select from_tz('20091101', 'ASIA/HONG_KONG');

查询结果:2009-11-01 00:00:00.000000 +08:00

  1. 函数 TZ_OFFSET
语法:TZ_OFFSET(timezone|[tz_name])

功能:返回给定的时区和标准时区(UTC)的偏移量。

TZ_OFFSET 的参数可以是:

  1. 一个合法的时区名,支持下列时区:
    {"Asia/Hong_kong", "+8:00"}:香港时间
    {"US/Eastern", "-4:00"}:美国东部时间
    {"Asia/Chongqing", "+08:00"}:重庆时间
    {"Etc/GMT-8", "+08:00"}:东八区
    {"Asia/Urumqi", "+08:00"}:乌鲁木齐时间
    {"Asia/Taipei", "+08:00"}:台北时间
    {"Asia/Macao", "+08:00"}:澳门时间
    {"Asia/Kashgar", "+08:00"}:喀什时间
    {"Asia/Harbin", "+08:00"}:哈尔滨时间
    {"Singapore", "+08:00"}:新加坡时间
    {"PRC", "+08:00"}:中国标准时间
  2. 一个与 UTC 标准时区的时间间隔
  3. SESSIONTIMEZONE 或 DBTIMEZONE

SELECT TZ_OFFSET(DBTIMEZONE);

查询结果为:+08:00

SELECT TZ_OFFSET('US/Eastern');

查询结果为:-04:00

  1. 函数 TRUNC
语法:TRUNC(date[, fmt])

功能:将日期时间 date 截断到最接近格式参数 fmt 指定的形式。若 fmt 缺省,则返回当天日期。语法与 ROUND 类似,但结果是直接截断,而不是四舍五入。参数及函数的返回类型与 ROUND 相同。参见 ROUND。

SELECT TRUNC(DATE '1992-10-27', 'scc');

查询结果为:1901-01-01

SELECT TRUNC(DATE '1992-10-27', 'YEAR') "FIRST OF THE YEAR";

查询结果为:1992-01-01

SELECT TRUNC(DATE '1992-10-27', 'q');

查询结果为:1992-10-01

SELECT TRUNC(DATE '1992-10-27', 'month');

查询结果为:1992-10-01

SELECT TRUNC(TIMESTAMP '1992-10-27 11:00:00', 'ww');

查询结果为:1992-10-21 00:00:00.000000

SELECT TRUNC(TIMESTAMP '1992-10-27 11:00:00', 'w');

查询结果为:1992-10-22 00:00:00.000000

SELECT TRUNC(TIMESTAMP '1992-10-27 12:00:01', 'ddd');

查询结果为:1992-10-27 00:00:00.000000

SELECT TRUNC(DATE '1992-10-27', 'day');

查询结果为:1992-10-25

SELECT TRUNC(TIMESTAMP '1992-10-27 12:00:31', 'hh');

查询结果为:1992-10-27 12:00:00.000000

SELECT TRUNC(TIMESTAMP '1992-10-27 12:00:31', 'mi');

查询结果为:1992-10-27 12:00:00.000000

  1. 函数 WEEK
语法:WEEK(date)

功能:返回指定日期属于所在年中的第几周。

SELECT WEEK(DATE '2003-02-10');

查询结果为:7

  1. 函数 WEEKDAY

语法:WEEKDAY(date)

功能:返回指定日期的星期值。如果是星期日则返回 0。

SELECT WEEKDAY(DATE '1998-10-26');

查询结果:1

  1. 函数 WEEKS_BETWEEN
语法:WEEKS_BETWEEN(date1,date2)

功能:返回两个日期之间相差周数。

SELECT WEEKS_BETWEEN(DATE '1998-2-28', DATE '1998-10-31');

查询结果:-35

  1. 函数 YEAR
语法:YEAR(date)

功能:返回日期中的年分量。

SELECT YEAR(DATE '2001-05-12');

查询结果为:2001

  1. 函数 YEARS_BETWEEN
语法:YEARS_BETWEEN(date1,date2)

功能:返回两个日期之间相差年数。

SELECT YEARS_BETWEEN(DATE '1998-2-28', DATE '1999-10-31');

查询结果为: -1

  1. 函数 LOCALTIME
语法:LOCALTIME (n)

功能:返回当前时间值,结果类型为 TIME。

参数:n:指定毫秒的精度。取值范围 0-6,默认为 6。

  1. 函数 LOCALTIMESTAMP
语法:LOCALTIMESTAMP (n)

功能:返回当前日期时间值,结果类型为 TIMESTAMP。

参数:n:指定毫秒的精度。取值范围 0-6,默认为 6。

  1. 函数 OVERLAPS
语法:OVERLAPS (date1,date2,date3,date4)

功能:返回两个时间段是否存在重叠,date1 为 datetime 类型、date2 可以为 datetime 类型也可以为 interval 类型,date3 为 datetime 类型,date4 可为 datetime 类型,也可以 interval 类型,判断(date1,date2),(date3,date4)有无重叠。其中 date2 与 date4 类型必须一致,如果 date2 为 interval
year to month,date4 也必须是此类型。结果类型为 BIT,若两个时间段存在重叠返回 1,不重叠返回 0。

SELECT OVERLAPS('2011-10-3','2011-10-9','2011-10-6','2011-10-13');

查询结果为:1

SELECT OVERLAPS('2011-10-3','2011-10-9','2011-10-10','2011-10-11');

查询结果为:0

SELECT OVERLAPS('2011-10-3',INTERVAL '09 23' DAY TO HOUR,'2011-10-10',INTERVAL
'09 23' DAY TO HOUR);

查询结果为:1

SELECT OVERLAPS('2011-10-3',INTERVAL '01 23' DAY TO HOUR,'2011-10-10',INTERVAL
'09 23' DAY TO HOUR);

查询结果为:0

SELECT OVERLAPS('2011-10-3',INTERVAL '1' YEAR TO MONTH,'2012-10-10',INTERVAL
'1-1' YEAR TO MONTH);

查询结果为:0

SELECT OVERLAPS('2011-10-3',INTERVAL '2' YEAR TO MONTH,'2012-10-10',INTERVAL
'1-1' YEAR TO MONTH);

查询结果为:1

49.函数 TO_CHAR

语法:TO_CHAR(date[,fmt[,nls]])

图例

函数 TO_CHAR(日期数据类型)

函数 TO_CHAR(日期数据类型)

功能:将日期数据类型 DATE 转换为一个在日期语法格式(FMT)中指定语法的 VARCHAR 类型字符串。若没有指定语法,日期 DATE 将按照缺省的语法转换为一个 VARCHAR 值。

FMT,NLS 的用法请参考函数 TO_DATE/TO_TIMESTAMP/TO_TIMESTAMP_TZ 用法。

DM 缺省的日期语法格式 FMT 为:'YYYY-MM-DD HH:MI:SS.FF6'。

SELECT TO_CHAR(SYSDATE,'YYYYMMDD');

查询结果:20110321 /* SYSDATE 为系统当前时间*/

SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD');

查询结果:2011/03/21

SELECT TO_CHAR(SYSDATE,'HH24:MI');

查询结果:16:56

SELECT TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS');

查询结果:20110321 16:56:19

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS');

查询结果:2011-03-21 16:56:28

SELECT TO_CHAR(INTERVAL '123-2' YEAR(3) TO MONTH) FROM DUAL;

查询结果:INTERVAL '123-2' YEAR(3) TO MONTH

select to_char(sysdate(), 'mon', 'NLS_DATE_LANGUAGE = ENGLISH');

查询结果:DEC

select to_char(sysdate(),'mon','NLS_DATE_LANGUAGE=''SIMPLIFIED CHINESE'' ');

查询结果:12 月

  1. 函数 SYSTIMESTAMP
语法:SYSTIMESTAMP (n)

功能:返回系统当前的时间戳,带数据库的时区信息。结果类型为 TIMESTAMP WITH TIME ZONE。

参数:n:指定毫秒的精度。取值范围 0-6,默认为 6

SELECT SYSTIMESTAMP();

查询结果为:2012-10-10 11:06:12.171000 +08:00

  1. 函数 NUMTODSINTERVAL
语法:NUMTODSINTERVAL (number, interval_unit)

功能:转换一个指定的 DEC 类型到 INTERVAL DAY TO SECOND

参数:

number:任何 dec 类型的值或者可以转换到 dec 类型的表达式

interval_unit:字符串限定 number 的类型: DAY、HOUR、MINUTE、或 SECOND

SELECT NUMTODSINTERVAL (2.5,'DAY');

查询结果为:INTERVAL '000000002 12:00:00.000000' DAY(9) TO SECOND(6)

  1. 函数 NUMTOYMINTERVAL
语法:NUMTOYMINTERVAL (number, interval_unit)

功能:转换一个指定的 DEC 类型值到 INTERVAL YEAR TO MONTH

参数:

number:任何 dec 类型的值或者可以转换到 dec 类型的表达式

interval_unit:字符串限定 number 的类型:YEAR 或 MONTH

SELECT NUMTOYMINTERVAL (2.5,'YEAR');

查询结果为:INTERVAL '000000002-06' YEAR(9) TO MONTH

  1. 函数 WEEK
语法:WEEK(date, mode)

功能:根据指定的 mode 返回日期为所在年的第几周

其中 mode 可取值及其含义见下表。

表8.3.5 mode取值及其含义
mode 值 周起始 返回值范围 说明
0 周日 0-53 本年第一个周日开始为第 1 周,之前算本年第 0 周
1 周一 0-53 本年第一个周一之前如果超过 3 天则算第 1 周,否则算第 0 周
2 周日 1-53 本年第一个周日开始为第 1 周,之前算去年第 5x 周
3 周一 1-53 本年第一个周一之前如果超过 3 天则算第 1 周,否则算去年第 5x 周;年末不足 4 天算明年第 1 周
4 周日 0-53 本年第一个周日之前如果超过 3 天则算第 1 周,否则算第 0 周
5 周一 0-53 本年第一个周一开始为第 1 周,之前算本年第 0 周
6 周日 1-53 本年第一个周日之前如果超过 3 天则算第 1 周,否则算去年第 5x 周;年末不足 4 天算明年第 1 周
7 周一 1-53 本年第一个周一开始为第 1 周,之前算去年第 5x 周

mode 的取值范围为-2147483648~2147483647,但在系统处理时会取 mode= mode%8。

由于 DM 支持儒略历,并考虑了历史上从儒略历转换至格里高利日期时的异常,不计算'1582-10-05'到'1582-10-14'之间的 10 天,因此 WEEK 函数对于 1582-10-15 之前日期的计算结果不能保证正确性。

SELECT WEEK('2013-12-31',0);

查询结果为:52

SELECT WEEK('2013-12-31',1);

查询结果为:53

SELECT WEEK('2013-12-31',2);

查询结果为:52

SELECT WEEK('2013-12-31',3);

查询结果为:51

  1. 函数 unix_timestamp
语法:UNIX_TIMESTAMP (d datetime)

功能:自标准时区的'1970-01-01 00:00:00 +0:00'到本地会话时区的指定时间的秒数差。如果为空,表示到当前时间。

参数:d 可以是一个 DATETIME、TIME、DATE、timestamp with time zone、timestamp with LOCAL time zone 类型(时区忽略,使用当前时区),也可以是一个字符串。或一个 YYYYMMDD、YYMMDD、YMMDD、YYYMMDD 格式的整形 BIGINT。

当前会话时区是 +8:00

SELECT UNIX_TIMESTAMP(timestamp '1970-01-01 08:00:00');

查询结果:0

SELECT UNIX_TIMESTAMP('1970-01-01 17:00:00');

查询结果:28800

SELECT UNIX_TIMESTAMP( 20120608 );

查询结果:1339084800

  1. 函数 from_unixtime
语法1:FROM_UNIXTIME (unixtime int)(bigint返回null)

功能:将自'1970-01-01 00:00:00'的秒数差转成本地会话时区的时间戳类型。

unixtime 为需要处理的参数(该参数是 Unix 时间戳),可以直接是 Unix 时间戳字符串。

select FROM_UNIXTIME ('539712061');

查询结果:1987-02-08 01:01:01

SELECT FROM_UNIXTIME(1249488000) ;

查询结果:2009-08-06 01:00:00

语法2:FROM_UNIXTIME (unixtime int,fmt varchar)

功能:将自'1970-01-01 00:00:00'的秒数差转成本地会话时区的指定 fmt 格式的时间串。

unixtime 为需要处理的参数(该参数是 Unix 时间戳),可以直接是 Unix 时间戳字符串(不支持)。

Fmt 见 DATE_FORMAT 中的 format 格式。

SELECT FROM_UNIXTIME( 1249488000 ,'%D') ;

查询结果:6th

  1. 函数 SESSIONTIMEZONE
语法:SESSIONTIMEZONE

功能:查看当前会话的时区

SELECT SESSIONTIMEZONE FROM DUAL;

查询结果:

行号 SESSIONTIMEZONE

1 +08:00

  1. 函数 DBTIMEZONE
语法:DBTIMEZONE

功能:查看当前数据库时区,即安装数据库时操作系统的时区。

SELECT DBTIMEZONE FROM DUAL;

查询结果:

行号 DBTIMEZONE

1 +08:00

  1. 函数 DATE_FORMAT
语法:DATE_FORMAT (d datetime, format varchar)

功能:以不同的格式显示日期/时间数据。

参数:

d:可以是可以是一个 DATETIME、TIME、DATE、timestamp with time zone、timestamp with LOCAL time zone 类型(时区忽略,使用当前时区)、一个 YYYYMMDD YYMMDD YMMDD YYYMMDD 格式的整形(不支持)。

format: 规定日期/时间的输出格式。格式有以下:27 个(NG 表示暂不支持)。非格式不做解释直接照抄。

表8.3.6 format释义
format 释义 备注
%a 缩写星期名
%b 缩写月名
%c 月,数值(0-12) 暂不支持
%D 带有英文前缀的月中的天
%d 月的天,数值(00-31)
%e 月的天,数值(0-31) 暂不支持
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟,数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23) 暂不支持
%l 小时 (1-12) 暂不支持
%M 月名
%m 月,数值(00-12)
%p AM 或 PM
%r 时间,12-小时(hh:mm:ss AM 或 PM)
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 星期(0……52), 这里星期一是星期的第一天 暂不支持
%W 星期名字(Sunday、Tuesday、Wednesday、Thursday、Friday、Saturday)
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%% 一个文字“%”

select date_format(timestamp '1980-1-1 1:1:1.123456789','%Y-%m-%d %H:%i:%s');

查询结果:1980-01-01 01:01:01

  1. 函数 TIME_TO_SEC
语法:TIME_TO_SEC (d datetime)

功能:将时间换算成秒

d 可以是一个 DATETIME、TIME、DATE、timestamp with time zone、timestamp with LOCAL time zone 类型(时区忽略,使用当前时区)。

select time_to_sec(timestamp '1900-1-1 23:59:59 +8:00');

查询结果:86399

select time_to_sec(time '23:59:59');

查询结果:86399

  1. 函数 SEC_TO_TIME
语法:SEC_TO_TIME (sec int)

功能:将秒换算成时间

select sec_to_time(104399);

查询结果: 28:59:59

  1. 函数 TO_DAYS
语法:TO_DAYS (d timestamp)

功能:转换成公元 0 年 1 月 1 日的天数差。

d 是要转换的日期时间类型。或一个 YYYYMMDD YYMMDD YMMDD YYYMMDD 格式的整形 BIGINT。

SQL> select to_days(now());

查询结果: 737614

  1. 函数 DATE_ADD
语法:DATE_ADD(d datetime, expr interval)

功能:返回一个日期或时间值加上一个时间间隔的时间值。

SQL> SELECT DATE_ADD('2020-07-12 12:20:30',INTERVAL '2 1 ' DAY TO SECOND);

查询结果: 2020-07-14 13:20:30.000000

  1. 函数 DATE_SUB
语法:DATE_SUB(d datetime, expr interval)

功能:返回一个日期或时间值减去一个时间间隔的时间值。

SQL> SELECT DATE_SUB('2020-07-12 12:20:30',INTERVAL '2 1 ' DAY TO SECOND);

查询结果: 2020-07-10 11:20:30.000000

8.4 空值判断函数

空值判断函数用于判断参数是否为 NULL,或根据参数返回 NULL。

1.函数 COALESCE

语法:COALESCE(n1,n2,…,nx)

功能:返回其参数中第一个非空的值,如果所有参数均为 NULL,则返回 NULL。如果参数为多媒体数据类型,如 TEXT 类型,则系统会将 TEXT 类型先转换为 VARCHAR 类型或 VARBINARY 类型,转换的最大长度为 8188,超过部分将被截断。

SELECT COALESCE(1,NULL);

查询结果:1

SELECT COALESCE(NULL,TIME '12:00:00',TIME '11:00:00');

查询结果:12:00:00

SELECT COALESCE(NULL,NULL,NULL,NULL);

查询结果:NULL

2.函数 IFNULL

语法:IFNULL(n1,n2)

功能:当表达式 n1 为非 NULL 时,返回 n1;若 n1 为 NULL,则返回表达式 n2 的值。若 n1 与 n2 为不同数据类型时,DM 会进行隐式数据类型转换,若数据类型转换出错,则会报错。

SELECT IFNULL(1,3);

查询结果:1

SELECT IFNULL(NULL,3);

查询结果:3

SELECT IFNULL('',2);

查询结果: (空串)

3.函数 ISNULL

语法:ISNULL(n1,n2)

功能:当表达式 n1 为非空时,返回 n1;若 n1 为空,则返回表达式 n2 的值。n2 的数据类型应能转为 n1 的数据类型,否则会报错。

SELECT ISNULL(1,3);

查询结果:1

4.函数 NULLIF

语法:NULLIF(n1,n2)

功能:如果 n1=n2,返回 NULL,否则返回 n1。

SELECT NULLIF(1,2);

查询结果:1

SELECT NULLIF(1,1);

查询结果:NULL

5.函数 NVL

语法:NVL(n1,n2)

功能:返回第一个非空的值。若 n1 与 n2 为不同数据类型时,DM 会进行隐式数据类型转换,若数据类型转换出错,则会报错。

说明:

  1. 当 n1 为确定性数据类型时,以 n1 为准;当 n1 的数据类型不确定时,以找到的第一个确定性数据类型为准;如果都为不确定数据类型时则定为 varchar 数据类型;
  2. 若参数一个为精确浮点数另一个为不精确浮点数,结果为不精确浮点数,可能导致数据精度丢失;
  3. 两个参数为不同数据类型时,结果为精度大的数据类型;
  4. 参数若为字符串类型,不论是 char 还是 varchar,结果类型均为 varchar,精度以大的为准;
  5. 参数类型都为时间日期类型时,结果类型为 n1 的类型;但若参数有 DATE 或 TIME 类型时,结果类型为 n1 和 n2 中精度较大的类型。

6.函数 NULL_EQU

语法:NULL_EQU(n1,n2)

功能:返回两个类型相同的值的比较,当 n1=n2 或 n1、n2 两个值中出现 null 时,返回 1。类型可以是 INT、BIT、BIGINT、FLOAT、DOUBLE、DEC、VARCHAR、DATE、TIME、TIME ZONE、DATETIME、DATETIME ZONE、INTERVAL 等。

select null_equ(1,1);

查询结果为:1

select null_equ(1,3);

查询结果为:0

select null_equ(1,null);

查询结果为:1

8.5 类型转换函数

1.函数 CAST

语法:CAST(value AS type)

功能:将参数 value 转换为 type 类型返回。类型之间转换的相容性如下表所示:表中,“允许”表示这种语法有效且不受限制,“-”表示语法无效,“受限”表示转换还受到具体参数值的影响。

数值类型为:精确数值类型和近似数值类型。

精确数值类型为:NUMERIC、DECIMAL、BYTE、INTEGER、SMALLINT。

近似数值类型为:FLOAT、REAL、DOUBLE PRECISION。

字符串为:变长字符串和固定字符串。

变长字符串为:VARCHAR、VARCHAR2。

固定字符串为:CHAR、CHARACTER。

字符串大对象为:CLOB、TEXT。

二进制为:BINARY、VARBINARY。

二进制大对象为:BLOB、IMAGE。

日期为:DATE。时间为:TIME。时间戳为:TIMESTAMP。

时间时区为:TIME WITH TIME ZONE。

时间戳时区为:TIMESTAMP WITH TIME ZONE。

年月时间间隔为:INTERVAL YEAR TO MONTH、INTERVAL YEAR、INTERVAL MONTH。

日时时间间隔为:INTERVAL DAY、INTERVAL DAY TO HOUR、INTERVAL DAY TO MINUTE、INTERVAL DAY TO SECOND、INTERVAL HOUR、INTERVAL HOUR TO MINUTE、INTERVAL HOUR TO SECOND、INTERVAL MINUTE、INTERVAL MINUTE TO SECOND、INTERVAL SECOND。

表8.5.1 CAST类型转换相容矩阵
Value type数据类型
数据类型 数值类型 字符串 字符串大对象 二进制 二进制大对象 日期 时间 时间戳 时间时区 时间戳时区 年月时间间隔 日时时间间隔
数值类型 受限 受限 允许 受限 受限 受限 受限 受限 
字符串 允许 允许 允许 允许 允许 受限 受限 受限 受限 受限 允许 允许
字符串大对象 允许 允许 -  -  - 
二进制 允许 允许 允许 允许 -  -  - 
二进制大对象 允许 -  -  - 
日期 允许 允许 允许 -  允许  - 
时间 允许 允许 允许 允许 允许 - 
时间戳 允许 允许 允许 允许 允许 允许 - 
时间时区 允许 允许 允许 允许
时间戳时区 允许 允许 允许 允许 -  允许  - 
年月时间间隔 允许 -  -  -  -  -  -  允许  - 
日时时间间隔 -  允许 -  -  -  -  -  -  -  -  -  允许 

SELECT CAST(100.5678 AS NUMERIC(10,2));

查询结果:100.57

SELECT CAST(100.5678 AS VARCHAR(8));

查询结果:100.5678

SELECT CAST('100.5678' AS INTEGER);

查询结果:101

SELECT CAST(INTERVAL '01-01' YEAR TO MONTH AS char(50));

查询结果:INTERVAL '1-1' YEAR(9) TO MONTH

2.函数 CONVERT

语法:CONVERT(type,value)

功能:将参数 value 转换为 type 类型返回。其类型转换相容矩阵与函数 CAST()的相同。

SELECT CONVERT(VARCHAR(8),100.5678);

查询结果:100.5678

SELECT CONVERT(INTEGER, '100.5678');

查询结果:101

SELECT CONVERT(CHAR(50),INTERVAL '100-5' YEAR(3) TO MONTH);

查询结果:INTERVAL '100-5' YEAR(3) TO MONTH

3.函数 HEXTORAW

语法:HEXTORAW (string)

功能:将由 string 表示的二进制字符串转换为一个 binary 数值类型。

SELECT HEXTORAW ('abcdef');

查询结果为:0xABCDEF

SELECT HEXTORAW ('B4EFC3CECAFDBEDDBFE2D3D0CFDEB9ABCBBE');

查询结果为:0xB4EFC3CECAFDBEDDBFE2D3D0CFDEB9ABCBBE

4.函数 RAWTOHEX

语法:RAWTOHEX (binary)

功能:将 RAW 类数值 binary 转换为一个相应的十六进制表示的字符串。binary 中的每个字节都被转换为一个双字节的字符串。
RAWTOHEX 和 HEXTORAW 是两个相反的函数。

SELECT RAWTOHEX('达梦数据库有限公司');

查询结果为:B4EFC3CECAFDBEDDBFE2D3D0CFDEB9ABCBBE

SELECT RAWTOHEX('13');

查询结果为:3133

5.函数 BINTOCHAR

语法:BINTOCHAR (binary)

功能:将数值 binary 转换为字符串。

SELECT BINTOCHAR ('0x61626364');

查询结果为:abcd

6.函数 TO_BLOB

语法:TO_BLOB (varbinary)

功能:将数值 varbinary 转换为 blob。

SELECT TO_BLOB(utl_raw.cast_to_raw('abcd'));

查询结果为:0x61626364

7.函数UNHEX

语法:UNHEX(char1)

功能:将十六进制格式的字符串转化为原来的格式字符串。

SELECT UNHEX('616263');

查询结果为:abc

8.HEX

语法:HEX(char1)

功能:将字符串转换为一个相应的十六进制表示的字符串。

SELECT HEX('abc');

查询结果为:616263

8.6 杂类函数

  1. 函数 DECODE
语法:DECODE(exp, search1, result1, … searchn, resultn[,default])

功能:查表译码,DECODE 函数将 exp 与 search1,search2, … searchn 相比较,如果等于 searchx,则返回 resultx,如果没有找到匹配项,则返回 default,如果未定义 default,返回 NULL。

SELECT DECODE(1, 1, 'A', 2, 'B');

查询结果为:'A'

SELECT DECODE(3, 1, 'A', 2, 'B');

查询结果为:NULL

SELECT DECODE(3, 1, 'A', 2, 'B', 'C');

查询结果为:'C'

  1. 函数 ISDATE
语法:ISDATE(exp)

功能:判断给定表达式是否为有效的日期,是返回 1,否则返回 0。

SELECT ISDATE('2012-10-9');

查询结果为:1

SELECT ISDATE('2012-10-9 13:23:37');

查询结果为:1

SELECT ISDATE(100);

查询结果为:0

  1. 函数 ISNUMERIC
语法:ISNUMERIC(exp)

功能:判断给定表达式是否为有效的数值,是返回 1,否则返回 0。

SELECT ISNUMERIC(1.323E+100);

查询结果为:1

SELECT ISNUMERIC('2a');

查询结果为:0

4.函数 DM_HASH

语法:DM_HASH (exp)

功能:根据给定表达式生成 HASH 值,返回结果为整型。

SELECT DM_HASH('DM HASH VALUE');

查询结果为:3086393668

SELECT DM_HASH(101);

查询结果为:1653893674

  1. 函数 LNNVL
语法:LNNVL(condition)

参数:condition 为布尔表达式。

功能:如果 condition 表达式计算结果值为 FALSE 或者 UNKNOWN 时,返回 TRUE,计算为 TRUE 时,则返回 FALSE。

SELECT LNNVL(1=0) ;

查询结果:1

SELECT T1.NAME, T2.NAME
FROM  PRODUCTION.PRODUCT_CATEGORY T1  RIGHT OUTER JOIN
PRODUCTION.PRODUCT_SUBCATEGORY T2
ON T1.PRODUCT_CATEGORYID = T2.PRODUCT_CATEGORYID WHERE LNNVL(T1.NAME<>'计算机');

查询结果:

NAME NAME
null 历史
计算机 计算机理论
计算机 计算机体系结构
计算机 操作系统
计算机 程序设计
计算机 数据库
计算机 软件工程
计算机 信息安全
计算机 多媒体
  1. 函数 LENGTHB
语法:LENGTHB(value)

参数:value 为 CHAR/VARCHAR/BINARY/VARBINARY/BLOB/CLOB 类型的值。

功能:返回 value 的字节数。

SELECT LENGTHB(0x1234567) "Length in bytes";

查询结果为:4

  1. 函数 FIELD
语法:函数FIELD(value, e1, e2, e3, e4...en)

功能:根据指定元素 value 在输入列表“e1、e2、e3、e4...en”中的位置返回相应的位置序号,不在输入列表时则返回 0。

FIELD()一般用在 ORDER BY 子句之后,将获取到的结果集按照输入列表的顺序进行排序。value 不在输入列表的结果,排在结果集的前面。

1)查询 50 在后面列表 10、50、100 中的位置序号。

SELECT field(50,10,50,100);

查询结果为:2

2)按照列表中指定的顺序输出结果集。不符合条件的结果放在结果集前面。

select * from PERSON.ADDRESS order by field(city,'武汉市洪山区','武汉市汉阳区','武汉市武昌区','武汉市江汉区');

查询结果为:

ADDRESSID	ADDRESS1     ADDRESS2	 CITY		 POSTALCODE
3	青山区青翠苑1号					武汉市青山区		430080
6	洪山区保利花园50-1-304				武汉市洪山区		430073
1	洪山区369号金地太阳城56-1-202   	   武汉市洪山区	   430073
16	洪山区光谷软件园C1_501				武汉市洪山区	    430073
13	洪山区关山春晓55-1-202				武汉市洪山区		430073
15	洪山区关山春晓11-1-202				武汉市洪山区		430073
7	洪山区保利花园51-1-702				武汉市洪山区		430073
8	洪山区关山春晓51-1-702				武汉市洪山区		430073
2	洪山区369号金地太阳城57-2-302	   武汉市洪山区	   430073
14	洪山区关山春晓10-1-202				武汉市洪山区		430073
5	汉阳大道熊家湾15号					武汉市汉阳区		430050
11	武昌区武船新村1号					武汉市武昌区		430063
4	武昌区武船新村115号					武汉市武昌区		430063
12	江汉区发展大道423号					武汉市江汉区		430023
10	江汉区发展大道555号					武汉市江汉区		430023
9	江汉区发展大道561号					武汉市江汉区		430023	 

8. 函数 ORA_HASH

语法:ORA_HASH(exp [,max_bucket [,seed_value]])

功能:为表达式 exp 生成 HASH 桶值。根据 exp 和随机数 seed_value 生成位于 0 到 max_bucket(包括 0 和 max_bucket)之间的 HASH 桶值,返回结果为整型。

参数:

exp:输入值。

max_bucket:返回的 HASH 桶值的最大值。取值范围为 0~4294967295,缺省为 4294967295。

seed_value:随机数。同一个 exp 搭配不同的 seed_value 会返回不同的结果(偶尔也会有巧合,得到相同值)。取值范围为 0~4294967295,缺省或 NULL 时为 0。

SELECT ORA_HASH('ORA HASH VALUE');

查询结果为:1038192070

SELECT ORA_HASH('ORA HASH VALUE',5);

查询结果为:4

SELECT ORA_HASH('ORA HASH VALUE',5,100);

查询结果为:1

SELECT ORA_HASH('ORA HASH VALUE',5,200);

查询结果为:2

SELECT ORA_HASH('ORA HASH VALUE',88,100);

查询结果为:14

微信扫码
分享文档
扫一扫
联系客服