oracle函數學習整理–字元處理

    一直以來很少整理在資料庫方面的知識，現在也該是時候整理一下。可惜突然不知道該從哪裡開始……想想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