oracle中的函数

oracle中函数主要有两种类型：

• 单行函数（single row function）：对一行进行操作，返回一个值，如对某一行进行大小写的转换
• 聚合函数（aggregate function）:对多行进行操作，返回一个值，如求和函数。（sql中需要having子句的原因是因为where子句中不能使用聚合函数）

 

单行函数主要有5种：

1. 字符函数：对由字符组成的字符串进行操作，结果返回字符串
2. 数字函数：对数字进行操作，结果返回数字
3. 转化函数：将一种数据类型转换成另外一种数据类型
4. 正则表达式函数：在查询数据时可以使用正则表达式
5. 日期函数：对日期进行处理

 

 

• 字符函数

1. ASCII(x)和CHR(x):ASCII(x)是获得字符x的ASCII码，CHR(x)是获得ASCII码为x的字符
2. CONCAT(x,y)：将y附加在x后面，该函数会返回得到的字符串（与||操作符功能相同）
3. INITCAP(x):将x中的每个单词的首字母转换成大写
4. INSTR(x,find_string [,start] [,occurence]):在x中查找find_string,INSTR()函数返回find_string所在的位置，该函数可以使用可选参数start来说明从x中的那个位置开始查找，还可以使用可选参数occurence说明应该返回find_string第几次出现的位置（注意：数据库中字符串中字符的编号是从1开始）
5. LENGTH(x):获取x中的字符个数
6. LOWER(x)和UPPER(x):LOWER(x)用于将x中的字母转换成小写，UPPER(x)用于将x中的字母转换成大写
7. LPAD(x,width [,pad_string]):用于在x的左边补齐空格，使x的总长度达到width个字符，如果在pad_string参数中指定了一个字符串，那么就用这个字符串来反复填充x左边的空位，以补齐x的长度。同理，RPAD(x,width [,pad_string])函数用于在x的右边补齐空字符串
8. LTRIM(x [,trim_string]),RTRIM(x [,trim_string]),TRIM():LTRIM(x [,trim_string])函数用于从x的左边截去一些字符，该函数还可以使用可选的参数trim_stirng来指定要截去的字符；如果没有指定trim_string参数，默认情况下会截去空格。同理，RTRIM函数用于从右边截去一些字符，TRIM用于从x的左边和右边各截去一些字符
9. NVL(x,value)：NVL函数用于将空值转换成一个已知的值。NVL(x,value)的结果如下：如果x为空，则返回value，否则返回x。
10. NVL2(x,value1,value2):函数的返回结果如下：如果x非空，则返回value1;否则返回value2.
11. REPLACE(x,search_string,replace_string)：用于在x中查找search_string，并将其替换为replace_string(REPLACE()函数并不会真正对数据库中的数据进行修改，而是只对结果集中返回的行进行修改）
12. SOUNDEX(x)：用于获取包含x发音的一个字符串，该函数用于对英文拼写不同但发音相似的单词进行比较
13. SUBSTR(x,start [,length]):用于从x中获取从start位置开始的一个子字符串，还可以使用可选参数length指定子字符串的长度

 

• 数字函数

1. ABS(x):返回x的绝对值
2. BITAND(x,y):返回对x和y进行位与操作的结果
3. CEIL(x):返回大于或等于x的最小整数
4. FLOOR(x):返回小于或等于x的最大整数
5. MOD(x,y):返回x除以y的对数
6. POWER(x,y):返回x的y次幂
7. ROUND(x [,y]):返回对x进行取整的结果；y为可选参数，说明对第几位小数取整。如果没有指定y，则对x在第o位小数处进行取整；如果y是负数，则对x在小数点的左边的第|y|处进行取整
8. SQRT(x):返回x的平方根
9. TRUNC(x [,y]):返回对x进行截断的结果；y为可选参数，说明对第几位小数进行截取。如果没有指定y，则对x在0位小数进行截取；如果y是负数，则对x在小数点左边的第|y|位处进行截取。（注意：只有大于5才进位）

 

 

•  正则表达式函数

    正则表达式中的元字符

1. \ :说明要匹配的字符是一个特殊字符，常量或者引用 例如：\n 匹配换行符
2. ^ :匹配字符串的开头位置 例如：如果A是字符串的第一个字符，^A匹配A
3. $ :匹配字符串的末尾位置 例如：如果B是字符串的最后一个字符，$B匹配B
4. * :匹配前面的字符0次或多次 例如：ba*rk可以匹配brk，bark，baark等等
5. + :匹配前面的字符1次或多次 例如： ba+rk可以匹配bark，baark等等
6. ? :匹配前面的字符0次或1次 例如： ba?rk可以匹配brk和bark
7. {n}:匹配一个字符恰好n次，其中n是一个整数 例如：hob{2}it可以匹配hobbit
8. {n,m}:匹配一个字符最少n次，最多m次，其中n和m都是整数例如：hob{2,3}it可以匹配hobbit和hobbbit
9. . :匹配除null之外的任意单个字符 例如：hob.it可以匹配hobait,hobbit等等
10. (pattern) :匹配指定模式的一个子表达式。可以使用子表达式构成复杂的正则表达式。在这种子表达式中，可以访问单词的匹配，称为匹配命中(captuer) 例如： anatom(y|ies)可以匹配anatomy和anatomies
11. x|y :匹配x或y，其中x和y是一个或多个字符 例如：war|peace 可以匹配war或peace
12. [abc] ：匹配中括号内的任意一个字符 例如：[ab]bc可以匹配abc和bbc
13. [a-z] ：匹配指定范围的任意一个字符 例如：[a-c]bc可以匹配abc，bbc和cbc
14. [::] ：指定一个字符类，可以匹配该类中的任何字符 例如：[:alphanum:] 可以匹配字符0-9,A-Z,a-z,[:alpha:]可以匹配字符A-Z和a-z,[:blank:]可以匹配空格或tab键,[:digit:]可以匹配数字0-9,[:graph:]可以匹配非空字符,[:lower:]可以匹配小写a-z,[:print:]与[:graph:]类似，不同之处在于包括空格字符,[:punct:]可以匹配标点符号.,"",[:space:]可以匹配所有的空字符,[:upper:]可以匹配所有的大学字母A-Z,[:xdigit:]可以匹配十六进制数字0-9，A-F和a-f
15. [..] :匹配一个组合元素，例如多字符元素
16. [==] :指定等价类
17. \n:这是对前一次匹配命中的一个后应用，其中n是一个正整数 例如(.)\1可以匹配连个连续相同的字符 

   oracle中的正则表达式函数

• REGEXP_LIKE(x,pattern [,match_option])

 

     当源字符串x匹配正则表达式pattern时，返回true。可以使用match_option修改默认匹配选项，该参数可以被设置为：

1. 'c',说明在进行匹配时区分大小写（默认选项）
2. 'i',说明在进行匹配时不区分大小写
3. 'n',允许使用可以匹配的任意字符的操作符
4. 'm',将x作为一个包含多行的字符串

• REGEXP_INSTR(x,pattern [,start [,occurrence [,return_ooption [,match_option]]]]) 在x中查找pattern，并返回pattern所在位置，可以指定以下可选参数：

1. start 开始的位置
2. occurence 说明应该返回第几次出现pattern的位置
3. return_option 说明应该返回什么整数，若该参数为0，则说明要返回的是x中的一个字符的位置；若该参数为非0的整数，则说明要返回的整数为x中出现在pattern之后的字符的位置
4. match_option ，修改默认的匹配设置

• REGEXP_REPLACE(x,pattern [,replace_string [,start [,occurence [,match_option]]]]) 在x中查找pattern，并将其替换为replace_string.其他选项的意思与REGEXP_INSTR()函数的参数完全相同

 

• REGEXP_SUBSTR(x.pattern [,start [,occurence [,match_option]]]) 返回x中可以匹配pattern中的一个子字符串，其开始位置由start指定，其他选项的意思与REGEXP_INSTR()函数的参数完全相同