Oracle常用函数

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