{aa24aa} 本文大纲
众所周知,Oracle 中系统函数特别多,貌似有好几百个,但实际上大部分函数对于普通应用而言,永远都用不到,本文将主要介绍 Oracle 中常见的数据类型函数。
据不完全统计,Oracle 中的字符函数总计约 40 个,主要功能包括:编解码、字符集转换、类型转换、检索、替换、连接、填充、统计等。可谓是功能强大、数量繁多,但相当一部分对于大多数应用来说都不大可能会用到,本节只简要介绍我本人认为比较实用的 23 个字符函数。函数名及语法如下:
序号 | 函数名 | 语法原型 | 常用 |
---|---|---|---|
1 | ASCII | ASCII(single_character) | × |
2 | ASCIISTR | ASCIISTR(string) | × |
3 | CHR | CHR(number_code) | × |
4 | CONCAT | CONCAT(string1, string2) | × |
5 | Concat with || | string1 || string2 [|| string_n] | √ |
6 | CONVERT | CONVERT(string1, char_set_to [, char_set_from] ) | × |
7 | DUMP | DUMP(expression [, return_format] [, start_position] [, length] ) | × |
8 | INITCAP | INITCAP(string1) | × |
9 | INSTR | INSTR(string, substring [, start_position [, th_appearance] ] ) | √ |
10 | LENGTH | LENGTH(string1) | √ |
11 | LENGTHB | LENGTHB(string1) | × |
12 | LOWER | LOWER(string1) | √ |
13 | LPAD | LPAD(string1, padded_length [, pad_string] ) | × |
14 | LTRIM | LTRIM(string1 [, trim_string] ) | √ |
15 | NCHR | NCHR(number_code) | × |
16 | REPLACE | REPLACE(string1, string_to_replace [, replacement_string] ) | √ |
17 | RPAD | RPAD(string1, padded_length [, pad_string] ) | × |
18 | RTRIM | RTRIM(string1 [, trim_string] ) | × |
19 | SUBSTR | SUBSTR(string, start_position [, length] ) | √ |
20 | TRANSLATE | TRANSLATE(string1, string_to_replace, replacement_string) | × |
21 | TRIM | TRIM([[ LEADING | TRAILING | BOTH] trim_character FROM ] string1 ) | √ |
22 | UPPER | UPPER(string1) | √ |
23 | VSIZE | VSIZE(expression) | × |
:返回指定字符的 ASCII 码。示例:
- ASCII
- SELECT ASCII('A') res FROM DUAL; -- res:65
- SELECT ASCII('ABC') res FROM DUAL; -- res:65
:返回指定字符串的 ASCII 码形式的字符串,非 ASCII 字符会被转化成 \xxxx 的形式({aa9aa},应该说这是个很好的思路,但不够严谨)。示例:
- ASCIISTR
- SELECT ASCIISTR('Oracle 数据库') res FROM DUAL; -- res:'Oracle \6570\636E\5E93'
&
- CHR
:返回指定 ASCII 码所代表的字符。
- NCHR
与
- CHR( number_code USING NCHAR_CS )
的转换效果相同。示例:
- NCHR
- SELECT CHR(65) res FROM DUAL; -- res:'A'
- SELECT CHR(30000) res FROM DUAL; -- res:'u0',注意与下两行做对比
- SELECT NCHR(30000) res FROM DUAL; -- res:'田'
- SELECT CHR(30000 USING NCHAR_CS) res FROM DUAL; -- res:'田'
&
- CONCAT
:将多个字符串连接成一个新的字符串并返回。由于
- ||
一次只能连接两个字符串,当要连接多个字符串时,就需要嵌套,既繁琐又不易阅读。事实上稍有经验的开发人员从不用
- CONCAT
函数,因为实际开发中经常需要连接多个字符串,这类需求用
- CONCAT
来实现效果要好得多。示例:
- ||
- SELECT CONCAT('A','B') res FROM DUAL; -- res:'AB'
- SELECT CONCAT('A',CONCAT('B','C')) res FROM DUAL; -- res:'ABC'
- SELECT 'A'||'B'||'C' res FROM DUAL; -- res:'ABC'
:将指定字符串从一种字符集转换为另一种字符集,并返回转换后的字符串。可用的字符集有:US7ASCII、WE8DEC、WE8HP、F7DEC、WE8EBCDIC500、WE8PC850、WE8ISO8859P1。这也许是 Oracle 中最坑的函数,因为一般编程语言或数据库中,叫这个名字的函数往往都是用来转换数据类型的,结果在 Oracle 中却是用来转换字符集的。示例:
- CONVERT
- SELECT CONVERT('Oracle','we8hp','f7dec') res FROM DUAL; -- res:Oracle
:将指定字符串按指定进制返回。示例:
- DUMP
- SELECT DUMP('Tech') res FROM DUAL; -- res:'Typ=96 Len=4: 84,101,99,104'
- SELECT DUMP('Tech',10) res FROM DUAL; -- res:'Typ=96 Len=4: 84,101,99,104'
- SELECT DUMP('Tech',16) res FROM DUAL; -- res:'Typ=96 Len=4: 54,65,63,68'
- SELECT DUMP('Tech',1016) res FROM DUAL; -- res:'Typ=96 Len=4 CharacterSet=ZHS16GBK: 54,65,63,68'
- SELECT DUMP('Tech',1017) res FROM DUAL; -- res:'Typ=96 Len=4 CharacterSet=ZHS16GBK: T,e,c,h'
:返回指定字符串的帕斯卡命名(即每个单词首字母都大写)副本。示例:
- INITCAP
- SELECT INITCAP('asp.net core') res FROM DUAL; -- res:'Asp.Net Core'
:返回第二个字符串在第一个字符串中出现的位置,可通过起始位置和长度来限定查找范围。示例:
- INSTR
- SELECT INSTR('Database','a',1,1) res FROM DUAL; -- res:2(第 1 次出现 a 的位置是 2)
- SELECT INSTR('Database','a',1,2) res FROM DUAL; -- res:4(第 2 次出现 a 的位置是 4)
- SELECT INSTR('Database','a',1,3) res FROM DUAL; -- res:6(第 3 次出现 a 的位置是 6)
- SELECT INSTR('Database','a',1,4) res FROM DUAL; -- res:0(第 4 次 a 没出现,所以是 0)
- SELECT INSTR('Database','a',3) res FROM DUAL; -- res:4(从第 3 个字母开始,第 1 次出现 a 的位置是 4)
- SELECT INSTR('Database','a',5) res FROM DUAL; -- res:6(从第 5 个字母开始,第 1 次出现 a 的位置是 6)
- SELECT INSTR('Database','a') res FROM DUAL; -- res:2(第 1 个字母开始,第 1 次出现 a 的位置是 2)
- SELECT INSTR('Database','A') res FROM DUAL; -- res:0(注意与上一行结果对比)
:返回指定字符串的长度。示例:
- LENGTH
- SELECT LENGTH(NULL) res FROM DUAL; -- res:NULL
- SELECT LENGTH('') res FROM DUAL; -- res:NULL
- SELECT LENGTH(' ') res FROM DUAL; -- res:1
- SELECT LENGTH('Asp.Net Core') res FROM DUAL; -- res:12
- SELECT LENGTH('数据库') res FROM DUAL; -- res:3
&
- LENGTHB
:返回指定字符串的字节数。经测试发现这两个函数的用法一样,结果也一样,示例:
- VSIZE
- SELECT LENGTHB(NULL) res FROM DUAL; -- res:NULL
- SELECT LENGTHB('') res FROM DUAL; -- res:NULL
- SELECT LENGTHB(' ') res FROM DUAL; -- res:1
- SELECT LENGTHB('Asp.Net Core') res FROM DUAL; -- res:12
- SELECT LENGTHB('数据库') res FROM DUAL; -- res:6
&
- LOWER
:返回指定字符串的小写或大写副本,示例:
- UPPER
- SELECT LOWER('Data Base') res FROM DUAL; -- res:'data base'
- SELECT UPPER('Data Base') res FROM DUAL; -- res:'DATA BASE'
- SELECT LPAD('ABC',2) res FROM DUAL; -- res:'AB'
- SELECT LPAD('ABC',4) res FROM DUAL; -- res:' ABC'
- SELECT LPAD('ABC',4,'$') res FROM DUAL; -- res:'$ABC'
&
- LTRIM
&
- RTRIM
:返回指定字符串去掉左边、右边或两边空格后的字符串。这个太简单就不提供示例代码了。实际上这 3 个函数都还有更高级的用法,分别如下:
- TRIM
:返回将 srcstr 字符串中的指定的 substr 字符去除后的字符串,其中 substr 只能是一个字符,否则直接语法报错,leading|trailing|both 分别指定将 srcstr 字符串中前面 | 后面 | 前后所有的 substr 字符去除。示例:
- TRIM(leading|trailing|both substr from srcstr)
- SELECT TRIM(leading 'a' FROM 'aaAaAaa') res FROM DUAL; -- res:'AaAaa'
- SELECT TRIM(trailing 'a' FROM 'aaAaAaa') res FROM DUAL; -- res:'aaAaA'
- SELECT TRIM(both 'a' FROM 'aaAaAaa') res FROM DUAL; -- res:'AaA'
、
- LTRIM(str1,str2)
:都将从 str1 字符串中去找 str2 中包含的字符,如果找到了就去除,如果出现多次都会被去除,但前提是 str2 字符串中第一个字符与 str1 字符串中第一个字符相同(rtrim 函数是与 str1 字符串中最后一个字符相同),返回最后剩下的字符串。示例:
- RTRIM(str1,str2)
- SELECT LTRIM('ABABA','A') res FROM DUAL; -- res:'BABA'
- SELECT LTRIM('ABABA','B') res FROM DUAL; -- res:'ABABA'
- SELECT LTRIM('ABABA','AB') res FROM DUAL; -- res:NULL
- SELECT LTRIM('ABABA','BA') res FROM DUAL; -- res:NULL
- SELECT LTRIM('ABABA','AC') res FROM DUAL; -- res:'BABA'
- SELECT LTRIM('ABABA','BC') res FROM DUAL; -- res:'ABABA'
:返回指定字符串被替换后的结果字符串。如果不指定替换字符串,将直接把原字符串中要替换的字符串删除。示例:
- REPLACE
- SELECT REPLACE('ABCABC','B','D') res FROM DUAL; -- res:'ADCADC'
- SELECT REPLACE('ABCABC','B') res FROM DUAL; -- res:'ACAC'
- SELECT REPLACE('ABCABC','b') res FROM DUAL; -- res:'ABCABC'(注意跟上一行结果做对比)
:返回从指定字符串中指定位置开始往后指定长度的字符串。如果不指定长度就会截取到字符串末尾为止;开始截取的位置必须指定,可以指定负数,负数时就会从后往前截取,指定 0 的效果与指定 1 的效果是一样的;如果起始位置值的绝对值大于字符串长度,返回值是 NULL。示例:
- SUBSTR
- SELECT SUBSTR('ABCD',1,2) res FROM DUAL; -- res:'AB'
- SELECT SUBSTR('ABCD',2,2) res FROM DUAL; -- res:'BC'
- SELECT SUBSTR('ABCD',2) res FROM DUAL; -- res:'BCD'
- SELECT SUBSTR('ABCD',-2) res FROM DUAL; -- res:'CD'
- SELECT SUBSTR('ABCD',0) res FROM DUAL; -- res:'ABCD'
- SELECT SUBSTR('ABCD',1) res FROM DUAL; -- res:'ABCD'
- SELECT SUBSTR('ABCD',5) res FROM DUAL; -- res:空
- SELECT SUBSTR('ABCD',-5) res FROM DUAL; -- res:空
:返回指定字符串被替换后的结果字符串。示例:
- TRANSLATE
- SELECT TRANSLATE('1tech23', '123', '456') res FROM DUAL; -- res:4tech56
- SELECT TRANSLATE('222tech', '2ec', '3it') res FROM DUAL; -- res:333tith
据不完全统计,Oracle 中的数字函数总计约 40 个,包含大量三角函数、指数函数、对数函数、进制转换函数、类型转换函数、近似值函数、聚合函数等数学函数。可谓是种类繁多,但大部分对于大多数应用来说都不太常用,其中类型转换函数、近似值函数、聚合函数将在后续文章中介绍,本节只简要介绍除此之外,我本人认为比较实用的 8 个数字函数。函数名及语法如下:
序号 | 函数名 | 语法原型 | 常用 |
---|---|---|---|
1 | ABS | ABS(number) | √ |
2 | CEIL | CEIL(number) | √ |
3 | FLOOR | FLOOR(number) | √ |
4 | GREATEST | GREATEST(expr1 [, expr2, ... expr_n] ) | × |
5 | LEAST | LEAST(expr1 [, expr2, ... expr_n] ) | × |
6 | MOD | MOD(m, n) | × |
7 | POWER | POWER(m, n) | × |
8 | SIGN | SIGN(number) | × |
:返回指定数字的绝对值。示例:
- ABS
- SELECT ABS(3888) res FROM DUAL; -- res:3888
- SELECT ABS(-3888) res FROM DUAL; -- res:3888
- SELECT ABS(23.65) res FROM DUAL; -- res:23.65
- SELECT ABS(23.65 * -1) res FROM DUAL; -- res:23.65
:返回大于或等于指定数字的最大整数。示例:
- CEIL
- SELECT CEIL(1.4) res FROM DUAL; -- res:2
- SELECT CEIL(1.5) res FROM DUAL; -- res:2
- SELECT CEIL(-1.5) res FROM DUAL; -- res:-1
:返回小于或等于指定数字的最小整数。示例:
- FLOOR
- SELECT FLOOR(1.4) res FROM DUAL; -- res:1
- SELECT FLOOR(1.5) res FROM DUAL; -- res:1
- SELECT FLOOR(-1.5) res FROM DUAL; -- res:-2
:返回参数列表中值最大的一项。参数既可以为数字,也可以是字符串,如果参数列表数据类型不一致会自动转型。示例:
- GREATEST
- SELECT GREATEST(2, 5, 12, 3) res FROM DUAL; -- res:12
- SELECT GREATEST('2', '5', '12', '3') res FROM DUAL; -- res:'5'
- SELECT GREATEST('apples', 'oranges', 'bananas') res FROM DUAL; -- res:'oranges'
- SELECT GREATEST('apples', 'applis', 'applas') res FROM DUAL; -- res:'applis'
:返回参数列表中值最小的一项。与
- LEAST
用法相同,结果相反。示例:
- GREATEST
- SELECT LEAST(2, 5, 12, 3) res FROM DUAL; -- res:2
- SELECT LEAST('2', '5', '12', '3') res FROM DUAL; -- res:'12'
- SELECT LEAST('apples', 'oranges', 'bananas') res FROM DUAL; -- res:'apples'
- SELECT LEAST('apples', 'applis', 'applas') res FROM DUAL; -- res:'applas'
:返回第一个参数除以第二个参数后的余数。示例:
- MOD
- SELECT MOD(15,4) res FROM DUAL; -- res:3
- SELECT MOD(15,0) res FROM DUAL; -- res:15
- SELECT MOD(11.6, 2) res FROM DUAL; -- res:1.6
- SELECT MOD(11.6, 2.1) res FROM DUAL; -- res:1.1
- SELECT MOD(-15, 4) res FROM DUAL; -- res:-3
- SELECT MOD(-15, 0) res FROM DUAL; -- res:-15
:返回第一个参数的第二个参数次方。示例:
- POWER
- SELECT POWER(5, 3) res FROM DUAL; -- res:125
- SELECT POWER(-5, 3) res FROM DUAL; -- res:-125
- SELECT POWER(6.2, 3) res FROM DUAL; -- res:238.328
- SELECT POWER(6.2, 3.5) res FROM DUAL; -- res:593.431934277892
:如果指定数字等于 0,则返回 0,如果大于 0,则返回 1,如果小于 0,则返回 -1。示例:
- SIGN
- SELECT SIGN(0) res FROM DUAL; -- res:0
- SELECT SIGN(5) res FROM DUAL; -- res:1
- SELECT SIGN(-5) res FROM DUAL; -- res:-1
据不完全统计,Oracle 中的日期函数总计约 20 个,主要是对日期的年、月、日、小时、分、秒等部分的操作和运算,还有取系统时间、会话时区等函数。对于普通应用而言,其中一大部分函数都不太常用,日期转型函数将在下一篇文章中介绍,本节只简要介绍我本人认为比较实用的 6 个日期函数。函数名及语法如下:
序号 | 函数名 | 语法原型 | 常用 |
---|---|---|---|
1 | ADD_MONTHS | ADD_MONTHS(date1, number_months) | √ |
2 | LAST_DAY | LAST_DAY(date) | √ |
3 | MONTHS_BETWEEN | MONTHS_BETWEEN(date1, date2) | × |
4 | NEXT_DAY | NEXT_DAY(date, weekday) | × |
5 | SYSDATE | SYSDATE | √ |
6 | SYSTIMESTAMP | SYSTIMESTAMP | × |
:返回在指定日期基础上再加指定个月后的日期。由于增加一个月往往不像增加 28~31 天那么简单,为了便于增加月,Oracle 专门实现了这样一个函数。示例:
- ADD_MONTHS
- SELECT ADD_MONTHS(fn_now,1) res FROM DUAL; -- res:2017-02-10 19:21:30
- SELECT ADD_MONTHS(fn_now,-1) res FROM DUAL; -- res:2016-12-10 19:21:30
:返回指定日期所在月份最后一天的日期,如果是长日期,返回日期的时间部分就是指定日期的时间部分。示例:
- LAST_DAY
- SELECT LAST_DAY(fn_today) res FROM DUAL; -- res:2017-01-31
- SELECT LAST_DAY(fn_now) res FROM DUAL; -- res:2017-01-31 19:21:30
:返回第一个日期参数减去第二个日期参数的结果,长日期会忽略时间部分,第一个日期大于第二个日期时返回正数,第一个日期小于第二个日期时返回负数,两个日期相同时返回 0,天数不同时返回小数。
- MONTHS_BETWEEN
- SELECT MONTHS_BETWEEN(fn_today,TO_DATE('2016-10-10','yyyy-mm-dd')) res FROM DUAL; -- res:3
- SELECT MONTHS_BETWEEN(fn_today,TO_DATE('2017-02-10','yyyy-mm-dd')) res FROM DUAL; -- res:-1
:返回指定日期之后的下一个星期几,假如指定日期是星期六,那指定日期之后下一个星期日就是当周的周日,而指定日期之后下一个星期一就是下周的周一,第二个参数的取值可能是:{'Sunday'/'Mondy'/'Tuesday'/'Wednesday'/'Thursday'/'Friday'/'Saturday'},也可能是:{'星期日'/'星期一'/'星期二'……}或{1/2/3……6/7},取决于你操作系统的本地语言支持。示例:
- NEXT_DAY
- SELECT fn_today res FROM DUAL; -- res:2017-01-10
- SELECT NEXT_DAY(fn_today,'Sunday') res FROM DUAL; -- res:2017-01-15
- SELECT NEXT_DAY(fn_today,'Monday') res FROM DUAL; -- res:2017-01-16
&
- SYSDATE
:返回当前系统时间,
- SYSTIMESTAMP
的返回值还包含 6 位的小数秒和时区。示例:
- SYSTIMESTAMP
- SELECT SYSDATE res FROM DUAL; -- res:2017-01-13 21:42:20
- SELECT SYSTIMESTAMP res FROM DUAL; -- res:13-1月 -17 09.40.34.235000 下午 +08:00
- SELECT TO_CHAR(SYSTIMESTAMP,'yyyy-mm-dd hh24:mi:ss.ff') res FROM DUAL; -- res:2017-01-13 21:42:33.266000
:返回变动后的日期。示例:
- INTERVAL
- SELECT fn_now+(INTERVAL '1' SECOND) res FROM DUAL; -- res:2017-01-10 19:21:31,加 1 秒钟
- SELECT fn_now+(INTERVAL '1' MINUTE) res FROM DUAL; -- res:2017-01-10 19:22:30,加 1 分钟
- SELECT fn_now+(INTERVAL '1' HOUR) res FROM DUAL; -- res:2017-01-10 20:21:30,加 1 小时
- SELECT fn_now+(INTERVAL '1' DAY) res FROM DUAL; -- res:2017-01-11 19:21:30,加 1 天
- SELECT fn_now+(INTERVAL '1' MONTH) res FROM DUAL; -- res:2017-02-10 19:21:30,加 1 个月
- SELECT fn_now+(INTERVAL '1' YEAR) res FROM DUAL; -- res:2018-01-10 19:21:30,加 1 年
- SELECT fn_now+(INTERVAL '1:1:1' HOUR TO SECOND) res FROM DUAL; -- res:2017-01-10 20:22:31,加指定时间
:将 d 转换为 INTERVAL YEAR TO MONTH 类型,c 的常见取值有'year'|'month'。
- NUMTOYMINTERVAL(d,c)
- SELECT fn_today+NUMTOYMINTERVAL(3,'year') res FROM DUAL; -- res:2020-01-10,加 3 年
:将 d 转换为 INTERVAL DAY TO SECOND 类型,c 的常见取值有'day'|'hour'|'minute'|'second'。
- NUMTODSINTERVAL(d,c)
- SELECT fn_today+NUMTODSINTERVAL(3,'day') res FROM DUAL; -- res:2017-01-13,加 3 天
本文主要介绍了常见的字符函数、数字函数和日期函数,其中部分函数在使用的时候需要注意操作系统的本地语言支持(NLS/Native Language Support),可以通过设置 NLS_LANG 环境变量来改变系统的本地语言支持。
来源: