Oracle 下常用函數的用法

//將欄位最後兩個字元去掉

select substr(t.modality,1,length(t.modality)-2),t.psi from order_list t where t.pl='LSS' or t.pl='PCS'

//在欄位後面添加尾碼字元

update product_type t  set t.code=CONCAT(t.code,'-1'),t.name=CONCAT(t.name,'-1') where t.pcode='LSS'

 

substr用法：

Oracle中的截取字串函數。

　　文法如下：

　　substr( string, start_position, [ length ] )

　　參數分析：

　　string

　　字串值

　　start_position（0和1都是表示截取的開始位置為第一個字元）

　　截取字串的初始位置， Number型，start_position為負數時，表示從字串右邊數起。

　　length

　　截取位元，Number型

　　其中，length為可選，如果length為空白（即不填）則返回start_position後面的所有字元。

　　意思就是：

　　從start_position開始,取出length個字元並返回取出的字串。

　　具體參考樣本。

　　樣本：

　　SELECT  substr('This is a test', 6, 2)  FROM test  返回 'is' 

　　substr('SyranMo have a dream', -5, 5)  返回 'dream' 

 

CONCAT用法:

 

      CONCAT(字串1, 字串2, 字串3, ...): 將字串1、字串2、字串3，等字串聯在一起。

      請注意，Oracle的C O NCAT()只允許兩個參數；換言之，一次只能將兩個字串串聯起來。

      不過，在Oracle中，我們可以用'||'來一次串聯多個字串。 

      SELECT CONCAT(region_name,store_name) FROM Geography 
      WHERE store_name = 'Boston'; 
      --'EastBoston'

 

       SELECT region_name || ' ' || store_name FROM Geography 
       WHERE store_name = 'Boston'; 
      --'East Boston'

 

 LTRIM用法

 

  LTRIM刪除起始空格後返回字元運算式。

  文法
  LTRIM ( character_expression )

  參數
  character_expression

  是字元或位元據運算式。character_expression 可以是常量、變數或列。character_expression 

  必 須是可以隱性轉換為 varchar 的資料類型。否則，使用 CAST 顯式轉換 character_expression。

  傳回型別
  varchar

 

 RTRIM用法

 

  RTRIM截斷所有尾隨空格後返回一個字串。

  文法
  RTRIM ( character_expression )

  參數
  character_expression

  由字元資料組成的運算式。character_expression 可以是常量、變數，也可以是字元或位元據的列

   傳回型別
   varchar

   注釋
   character_expression 必須為可隱性轉換為 varchar 的資料類型。否則請使用 CAST 函數顯式轉換   

    character_expression。

 

TO_NUMBER用法

 

將一個字串轉為數字類型

 

 

 

trunc  的用法

文法：

trunc ( date, [ format ] )

 

例子：

 

  --Oracle trunc()函數的用法
/**************日期********************/
1.select trunc(sysdate) from dual  --2011-3-18  今天的日期為2011-3-18
2.select trunc(sysdate, 'mm')   from   dual  --2011-3-1    返回當月第一天.
3.select trunc(sysdate,'yy') from dual  --2011-1-1       返回當年第一天
4.select trunc(sysdate,'dd') from dual  --2011-3-18    返回當前年月日
5.select trunc(sysdate,'yyyy') from dual  --2011-1-1   返回當年第一天
6.select trunc(sysdate,'d') from dual  --2011-3-13 (星期天)返回當前星期的第一天
7.select trunc(sysdate, 'hh') from dual   --2011-3-18 14:00:00   目前時間為14:41   
8.select trunc(sysdate, 'mi') from dual  --2011-3-18 14:41:00   TRUNC()函數沒有秒的精確
/***************數字********************/
/*
TRUNC（number,num_digits） 
Number 需要截尾取整的數字。 
Num_digits 用於指定取整精度的數字。Num_digits 的預設值為0。
TRUNC()函數截取時不進行四捨五入
*/
9.select trunc(123.458) from dual --123
10.select trunc(123.458,0) from dual --123
11.select trunc(123.458,1) from dual --123.4
12.select trunc(123.458,-1) from dual --120
13.select trunc(123.458,-4) from dual --0
14.select trunc(123.458,4) from dual  --123.458
15.select trunc(123) from dual  --123
16.select trunc(123,1) from dual --123
17.select trunc(123,-1) from dual --120

replace 用法

REPLACE（'2012/09/08','/','-'）==>2012-09-08

TRANSLATE 用法

TRANSLATE ( 'char' , 'from_string' , 'to_string' )

TRANSLATE返回將from_string中的每個字元替換為to_string中的相應字元以後的string。

TRANSLATE是REPLACE所提供的功能的一個超集。如果from_string比to_string長，那麼在from_string中而不在to_string中的額外字元將從char中被刪除，因為它們沒有相應的替換字元。to_string不可為空。Oracle將Null 字元串解釋為NULL，並且如果TRANSLATE中的任何參數為NULL，那麼結果也是NULL。（TRANSLATE (’please go away’, ‘a’, NULL) ==> NULL）

舉個例子 

select translate('123abc','2dc','4e') from dual;

因為from_string和to_string的位置是一一對應的，2對應4，d對應e，c沒有對應的值，所以c應該會被刪除。

所以例子的字元裡的2會替換為4，

d因為字串裡沒有，所以不作替換，

c由於沒有對應的替換字元，所以字串裡的c會被刪除

那麼可以得出，結果是

143ab

INITCAP()

　　假設c1為一字串.函數INITCAP()是將每個單詞的第一個字母大寫,其它字母變為小寫返回.

　　單詞由空格,控制字元,標點符號等非字母符號限制.

　　例:select INITCAP('vEni,vedi,viCI') Ceasar from dual

　　結果為 Ceasar

 　　Veni,Vedi,Vici

rpad函數

rpad函數從右邊對字串使用指定的字元進行填充 　　
rpad(string,padded_length,[pad_string]) 　　
string 表示：被填充的字串 　　
padded_length 表示：字元的長度，是返回的字串的數量，如果這個數量比原字串的長度要短，rpad函數將會把字串截取成從左至右的n個字元; 　　
pad_string 是個選擇性參數，這個字串是要粘貼到string的右邊，如果這個參數未寫，lpad函數將會在string的右邊粘貼空格。 　　
例如： 　　
rpad('tech', 7); 將返回'tech ' 　　
rpad('tech', 2); 將返回'te' 　　
rpad('tech', 8, '0'); 將返回'tech0000' 　　
rpad('tech on the net', 15, 'z'); 將返回 'tech on the net' 　　
rpad('tech on the net', 16, 'z'); 將返回 'tech on the netz'

SQL> select rpad('aa',5)||decode('bbb',null,' ',rpad('bbb',8))||rpad('cccc',12) from dual;
 
RPAD('AA',5)||DECODE('BBB',NUL
------------------------------
aa   bbb     cccc

 

 

instr函數

在Oracle/PLSQL中，instr函數返回要截取的字串在源字串中的位置。文法如下： 
　　 instr( string1, string2 [, start_position [, nth_appearance ] ] )  string1源字串，要在此字串中尋找。string2要在string1中尋找的字串 。start_position代表string1 的哪個位置開始尋找。此參數可選，如果省略預設為1. 字串索引從1開始。如果此參數為正，從左至右開始檢索，如果此參數為負，從右至左檢索，返回要尋找的字串在源字串中的開始索引。nth_appearance代表要尋找第幾次出現的string2. 此參數可選，如果省略，預設為 1.如果為負數系統會報錯。

 

注意： 
　　位置索引號從1開始。
　　如果String2在String1中沒有找到，instr函數返回0。
　　樣本： 
　　SELECT instr('syranmo','s') FROM dual; -- 返回 1 
　　SELECT instr('syranmo','ra') FROM dual; -- 返回 3 
　　SELECT instr('syran mo','a',1,2) FROM dual; -- 返回
0