一直以来很少整理在数据库方面的知识，现在也该是时候整理一下。可惜突然不知道该从哪里开始……想想Oracle和SQL Server给我的第一印象区别就是他们的构架，这东西我还是搞不太清楚只是稍微有点点感觉而已，算了还是先整理一下函数吧，免的学点又丢点……

字符处理函数（常用的）

1、Initcap(char):将char中空格格开的单词首字母大写

Examples
The following example capitalizes each word in the string,char can be of any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The return value is the same datatype as char.

SELECT INITCAP('the soap') "Capitals" FROM DUAL;

Capitals
---------
The Soap
 
2、lower(char) / upper(char) :将char全部变成小写 / 将char全部变成大写

 Examples LOWER / UPPER returns char, with all letters lowercase. char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the same datatype as char. --小写The following example returns a string in lowercase: SELECT LOWER('MR. SCOTT MCMILLAN') "Lowercase" FROM DUAL;

Lowercase
--------------------
mr. scott mcmillan  --大写The following example returns a string in uppercase:SELECT UPPER('Large') "Uppercase" FROM DUAL;

Upper
-----
LARGE

补充:由于Oracle有一个特性就是添加进去的记录数据，它会自动转化为大写，因此比如做模糊查询的时候，可能需要查找姓名以"j"开头的人。 Select * from employee where name like 'j%' --这样是查不到数据的 这个时候可以用到上面的函数 Select * from employee where name like upper('j%') 同样也可以用这个方法 Select * from employee where lower(name) like 'j%'

 

 

3、Ltrim(char,set) / Rtrim(char,set):在char中替换set中的字符

 

Examples
The following example trims the letters "xy" from the right side of a string:

SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM example"
FROM DUAL;
 
RTRIM examp
-----------
BROWNINGyxX

个人感觉Oracle带的这个例子并不能说明什么东西，于是补充一个：select Rtrim('xybroxydxy','xy') "X" from dual; RTRIM X -------- xybroxyd select Rtrim('xybroxy1xy1','xy') "X" from dual; RTRIM X ----------- xybroxy1xy1 可以看出只要set不出现在char的右边，那么就不会出现替换。（Ltrim同理）

4、Translate(char,from,set):把char中的from字符用set替换，from与set one-by-one对应

You cannot use an empty string for to_string to remove all characters in from_string from the return value. Oracle interprets the empty string as null, and if this function has a null argument, then it returns null.

Examples
The following statement translates a license number. All letters 'ABC...Z' are translated to 'X' and all digits '012 . . . 9' are translated to '9':

SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "License"
     FROM DUAL;
 
License
--------
9XXX999

The following statement returns a license number with the characters removed and the digits remaining:

SELECT TRANSLATE('2KRW229',
   '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')
   "Translate example"
     FROM DUAL;
 
Translate example
-----------------
2229

 

5、Replace(char,searchString,set):在char中查找searchString字符串用set来替换

 
 

Examples

The following example replaces occurrences of "J" with "BL":

SELECT REPLACE('JACK and JUE','J','BL') "Changes"
     FROM DUAL;

Changes
--------------
BLACK and BLUE

select replace('1212xy2323xy23','xy','**') from dual; REPLACE('1212X -------------- 1212**2323**23

Replace 与 trunslate 的区别：
The Replace function provides functionality related to that provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-one substitution. REPLACE lets you substitute one string for another as well as to remove character strings.

 

6、TRIM
( [{ { LEADING | TRAILING | BOTH } [trim_character])
   | trim_character
   }
   FROM
  ]
  trim_source
)

TRIM enables you to trim leading or trailing characters (or both) from a character string. If trim_character or trim_source is a character literal, then you must enclose it in single quotes.
Both trim_character and trim_source can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype and is in the same character set as trim_source.

Examples

This example trims leading and trailing zeroes from a number:

SELECT TRIM (0 FROM 0009872348900) "TRIM Example"
   FROM DUAL;

TRIM Example
------------
    98723489