oracle函數-LPAD/RPAD

標籤：cti   xxx   round   src   span   字串   terminal   target   陷阱   

 

基本用法：

lpad函數

函數介紹lpad函數是Oracle資料庫函數，lpad函數從左邊對字串使用指定的字元進行填充。從其字面意思也可以理解，l是left的簡寫，pad是填充的意思，所以lpad就是從左邊填充的意思。2文法 文法格式如下：lpad( string, padded_length, [ pad_string ] ) string準備被填充的字串； padded_length填充之後的字串長度，也就是該函數返回的字串長度，如果這個數量比原字串的長度要短，lpad函數將會把字串截取成從左至右的n個字元; pad_string填充字串，是個選擇性參數，這個字串是要粘貼到string的左邊，如果這個參數未寫，lpad函數將會在string的左邊粘貼空格。 樣本1：SQL> select lpad(‘abcde‘,10,‘x‘) from dual;LPAD(‘ABCDE‘,10,‘X‘)--------------------xxxxxabcde 樣本2：SQL> select lpad(‘abcde‘,10,‘oq‘) from dual;LPAD(‘ABCDE‘,10,‘OQ‘)---------------------oqoqoabcde 樣本3：SQL> select lpad(‘abcde‘,2) from dual;LPAD(‘ABCDE‘,2)---------------ab~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~與lpad函數對應的是rpad函數：rpad函數從右邊對字串使用指定的字元進行填充，文法格式與lpad格式相同：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‘  陷阱處理：

 

首先看下Oracle官方對函數的定義：

The RPAD function returns an expression, right-padded to a specified length with the specified characters; or, when the expression to be padded is longer than the length specified after padding, only that portion of the expression that fits into the specified length.

RPAD函數將指定的字串從目標字串的右側填充指定長度的字串，若待填充的字串的長度大於制定的填充長度，則會根據填充的長度對目標字串進行截取。

 

函數文法

RPAD (text-exp , length [, pad-exp])

 

對Length填充長度的解釋

關於對各個參數的解釋，我們重點來看看length這個參數：

Length

The total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multibyte character sets, the display length of a character string can differ from the number of characters in the string.

When you specify a value for length that is shorter than the length of text-exp, then this function truncates the expression to the specified length.

粗體部分大意為：傳回值的總長度正如它們在你的終端螢幕上顯示的長度一樣，注意，是終端顯示的長度，非該字串在資料庫中的實際顯示長度（位元組）。這在多位元組支援的資料庫（如簡體中文，AL32UTF8等等）中尤為明顯。

通過下面的一個樣本來理解一下這句話:

SQL> SELECT ‘甲骨文‘ ORA_STR,  2         LENGTHB(‘甲骨文‘) ORA_STR_LENGTH,  3         RPAD(‘甲骨文‘, 10, ‘$‘) ORA_RPAD_STR,  4         LENGTHB(RPAD(‘甲骨文‘, 10, ‘$‘)) ORA_RPAD_STR_LENGTH  5    FROM dual  6  ; ORA_STR   ORA_STR_LENGTH ORA_RPAD_STR  ORA_RPAD_STR_LENGTH--------- -------------- ------------- -------------------甲骨文                 9 甲骨文$$$$                     13 SQL> 

我的資料庫的字元集是AL32UTF8，所以一個中文字元佔3個位元組。在這個例子中，LENGTHB函數對字串“甲骨文”的位元組長度計算很準確，為9個位元組，但是你會發現，我們用RPAD函數向字串”甲骨文“後面填充$符號，總長度為10時，結果範圍的卻是”甲骨文$$$$"，並不是我們期望得到的值"甲骨文$"，同時也會發現，截取完後的字串長度也並不是10個位元組。

我們回到官方定義中對於返回長度Length的解釋，這個長度取決於當前字元在螢幕終端顯示時所佔據的大小，對於中文來說，通常在螢幕上佔據的是2個位元組的寬度：

因此RPAD在進行填充的時候對這些字元按照兩個位元組去處理，所以出現了上述樣本所描述的情況。這種不可預知（Sometimes）的情況對於那些輸出固定位元組寬度的程式來說無疑會產生問題。

 

解決方案

下面是針對這種問題的解決方案：

SQL> SELECT ‘甲骨文‘ ORA_STR,  2         LENGTHB(‘甲骨文‘) ORA_STR_LENGTH,  3         RPAD(‘甲骨文‘, 10, ‘$‘) ORA_RPAD_STR,  4         LENGTHB(RPAD(‘甲骨文‘, 10, ‘$‘)) ORA_RPAD_STR_LENGTH,  5         -- 解決方案  6         SUBSTRB(‘甲骨文‘ || RPAD(‘$‘, 10, ‘$‘), 1, 10) SOLUTION  7    FROM dual  8  ; ORA_STR   ORA_STR_LENGTH ORA_RPAD_STR  ORA_RPAD_STR_LENGTH SOLUTION--------- -------------- ------------- ------------------- ----------甲骨文                 9 甲骨文$$$$                     13 甲骨文$ SQL> 

 

在實際應用中，我們可以將上述方法抽象為一個函數來使用：

CREATE OR REPLACE FUNCTION rpad2(str IN VARCHAR2,               len IN PLS_INTEGER,               pad IN VARCHAR2)  RETURN VARCHAR2ISBEGIN  RETURN SUBSTRB(str || RPAD(pad, len, pad), 1, len);END rpad2;

使用範例：

SQL> SELECT rpad2(‘甲骨文‘, 10, ‘$‘) PADDED_VALUE FROM DUAL; PADDED_VALUE-----------------------甲骨文$ SQL> 

 

 參考連結

1. Oracle Bug? RPAD of Japanese (kanji) character in Oracle 10gR2 UTF8 database
2. Issue with LPAD/RPAD when using with Japanese data
3. Oracle Documentation - RPAD

oracle函數-LPAD/RPAD