Oracle常用函数
来源:互联网 发布:电子商务美工论文范文 编辑:程序博客网 时间:2024/06/08 03:23
CONCAT() 拼接两个字符串(也可使用连接符"||"实现同样的效果)
语法
CONCAT()(string1, string2)
示例
CONCAT('a', 'b') --返回'ab'
LENGTH() 计算给定字符串的长度
语法
LENGTH(string1)
示例
LENGTH(NULL) would return NULL
LENGTH('') would return NULL
LENGTH(' ') would return 1
LTRIM() 从字符串的左侧移除特定字符
语法
LTRIM(string1, [trim_string])
示例
LTRIM(' tech') --返回'tech'
LTRIM('000123', '0'); --返回'123'
LTRIM('6372Tech', '0123456789') --返回'Tech'
RTRIM() 从字符串的右侧移除特定字符
语法
RTRIM(string1, [trim_string])
示例
RTRIM('tech '); --返回'tech'
RTRIM('123000', '0'); --返回'123'
RTRIM('Tech6372', '0123456789'); --返回'Tech'
TRIM() 从字符串的开始或结束处移除特定字符
语法
TRIM([leading|trailing|both [trim_character] string1 )
示例
TRIM(' tech ') would return 'tech'
TRIM(' ' from ' tech ') would return 'tech'
TRIM(leading '0' from '000123') would return '123'
TRIM(trailing '1' from 'Tech1') would return 'Tech'
TRIM(both '1' from '123Tech111') would return '23Tech'
NVL() 当字段值为NULL时,用另外一个值替代
语法
NVL(string1, replace_with)
示例
select NVL(supplier_city, 'n/a') from suppliers;
select supplier_id, NVL(supplier_desc, supplier_name) from suppliers;
NVL2() 当字段值为NULL或非NULL时,用其他值替代
NVL2(string1, value_if_NOT_null, value_if_null)
示例
select NVL2(supplier_city, 'Completed', 'n/a') from suppliers;
select supplier_id, NVL2(supplier_desc, supplier_name, supplier_name2) from suppliers;
SUBSTR() 返回给定字符串的子串
语法
SUBSTR(string, start_position, [length])
示例
SUBSTR('This is a test', 6, 2) --返回'is'
SUBSTR('This is a test', 6) --返回'is a test'
SUBSTR('TechOnTheNet', -6, 3) --返回'The'
TO_CHAR() 数值或日期转换为字符串
语法
TO_CHAR(value, [format_mask], [nls_language])
示例
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;
TO_CHAR(1210.73, '$9,999.00') --返回'$1,210.73'
TO_DATE() 字符串转换为日期
语法
TO_DATE(string1, [format_mask], [nls_language])
示例
select to_date('2013-01-01 12:00:00','yyyy-mm-dd hh24:mi:ss') from dual;
DECODE 根据条件进行对应解析
语法
DECODE(expression , search, result [, search, result]... [, default] )
示例
SELECT supplier_name, DECODE(supplier_id, 10000, 'IBM', 10001, 'Microsoft',10002, 'Hewlett Packard', 'Gateway') result
FROM suppliers;
CASE 根据条件进行对应解析
CASE [expression]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END
示例
select table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from all_tables;
select table_name,
CASE
WHEN owner='SYS' THEN 'The owner is SYS'
WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from all_tables;
更多函数可查阅 http://www.techonthenet.com/oracle/functions/index.php
语法
CONCAT()(string1, string2)
示例
CONCAT('a', 'b') --返回'ab'
LENGTH() 计算给定字符串的长度
语法
LENGTH(string1)
示例
LENGTH(NULL) would return NULL
LENGTH('') would return NULL
LENGTH(' ') would return 1
LTRIM() 从字符串的左侧移除特定字符
语法
LTRIM(string1, [trim_string])
示例
LTRIM(' tech') --返回'tech'
LTRIM('000123', '0'); --返回'123'
LTRIM('6372Tech', '0123456789') --返回'Tech'
RTRIM() 从字符串的右侧移除特定字符
语法
RTRIM(string1, [trim_string])
示例
RTRIM('tech '); --返回'tech'
RTRIM('123000', '0'); --返回'123'
RTRIM('Tech6372', '0123456789'); --返回'Tech'
TRIM() 从字符串的开始或结束处移除特定字符
语法
TRIM([leading|trailing|both [trim_character] string1 )
示例
TRIM(' tech ') would return 'tech'
TRIM(' ' from ' tech ') would return 'tech'
TRIM(leading '0' from '000123') would return '123'
TRIM(trailing '1' from 'Tech1') would return 'Tech'
TRIM(both '1' from '123Tech111') would return '23Tech'
NVL() 当字段值为NULL时,用另外一个值替代
语法
NVL(string1, replace_with)
示例
select NVL(supplier_city, 'n/a') from suppliers;
select supplier_id, NVL(supplier_desc, supplier_name) from suppliers;
NVL2() 当字段值为NULL或非NULL时,用其他值替代
NVL2(string1, value_if_NOT_null, value_if_null)
示例
select NVL2(supplier_city, 'Completed', 'n/a') from suppliers;
select supplier_id, NVL2(supplier_desc, supplier_name, supplier_name2) from suppliers;
SUBSTR() 返回给定字符串的子串
语法
SUBSTR(string, start_position, [length])
示例
SUBSTR('This is a test', 6, 2) --返回'is'
SUBSTR('This is a test', 6) --返回'is a test'
SUBSTR('TechOnTheNet', -6, 3) --返回'The'
TO_CHAR() 数值或日期转换为字符串
语法
TO_CHAR(value, [format_mask], [nls_language])
示例
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;
TO_CHAR(1210.73, '$9,999.00') --返回'$1,210.73'
TO_DATE() 字符串转换为日期
语法
TO_DATE(string1, [format_mask], [nls_language])
示例
select to_date('2013-01-01 12:00:00','yyyy-mm-dd hh24:mi:ss') from dual;
DECODE 根据条件进行对应解析
语法
DECODE(expression , search, result [, search, result]... [, default] )
示例
SELECT supplier_name, DECODE(supplier_id, 10000, 'IBM', 10001, 'Microsoft',10002, 'Hewlett Packard', 'Gateway') result
FROM suppliers;
CASE 根据条件进行对应解析
CASE [expression]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END
示例
select table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from all_tables;
select table_name,
CASE
WHEN owner='SYS' THEN 'The owner is SYS'
WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from all_tables;
更多函数可查阅 http://www.techonthenet.com/oracle/functions/index.php
0 0
- 常用oracle之常用函数
- Oracle常用函数
- Oracle 常用函数
- Oracle 常用函数
- oracle常用函数问答
- Oracle 常用SQL函数
- Oracle 常用SQL函数
- Oracle常用函数
- Oracle 常用SQL函数
- oracle常用函数
- (转载)Oracle 常用函数
- Oracle 常用SQL函数
- Oracle 常用函数
- Oracle 常用函数
- oracle常用函数
- 常用oracle函数
- Oracle常用函数
- oracle常用函数
- ICE资料
- 持续集成(三)- hudson插件入门
- 金额转中文大写
- 印度政府计划在2013年出台约合140元人民币的平板电脑
- 职业生涯中的五个坎该如何跨越
- Oracle常用函数
- 使用BeanPostProcessor接口对受管Bean进行预处理
- 持续集成(四)hudson - 插件管理
- div+css打造兼容ie6的tab样式
- 【索引】CodeForces Round #222 (Div. 2)
- GCC Command Options
- Critical Section(关键区域,临界区域)
- Eclipse中使用自己的makefile管理工程
- linux pthread 状态和pthread终止