常用字串函數

#	功能	MSSQL	Oracle	說明
1	字串連結	+、 CONCAT (2012) FORMATMESSAGE (2016)	||、CONCAT	字串連結。 S: 使用加號（+）及 CONCAT函數(2012)。 O: 使用||及CONCAT函數。
2	取得部分子字串	SUBSTRING LEFT RIGHT	SUBSTR	取得字串指定算式中的特定段落。 O: SUBSTR可指定起始位置由左或右起算，並取得特定長度。 S: 則提供三種擷取部分字串的函數。
3	字串取代	REPLACE STUFF TRANSLATE (2017)	REPALACE TRANSLATE	以新字串取代目標字串中的特定（舊）字串。
4	字串位置	CHARINDEX PATINDEX	INSTR	搜尋字串中特定字串的開始位置。
5	字串填補	SPACE FORMATMESSAGE (2016)	LPAD RPAD	在字串左側或右側以特定字元填補至特定長度。
6	字串長度	LEN DATALENGTH	LENGTH LENGTHB	取得字串中字元數量。
7	刪除空白 (特定字元)	RTRIM LTRIM TRIM (2017改版)	RTRIM LTRIM TRIM	刪除字串前後空白(O尚支援特定字元)。 S: 提供RTRIM()及LTRIM()兩個函數。 O: 除提供與相同指令外（O除空白外，尚支援特定字元），尚支援可同時移除前後空白字元的TRIM()函數。
8	字串大小寫	LOWER UPPER	LOWER UPPER INITCAP	LOWER: 轉換為小寫字元。 UPPER: 轉換為大寫字元。 INITCAP: 第一個字元轉換為大寫。

1. 字串連結

ORACLE可使用「||」及CONCAT函數兩種方法，其中「|」稱為管線（Pipe）符號。

MSSQL則直接使用加號（+），2012版本也支援CONCAT函數。當使用加號時，且資料為數值時，需先將數值型態資料轉換為字元型態資料再進行字串連結。

測試	值1	值2	MSSQL	ORACLE	結果
字串	A	B	SELECT 'A'+'B'	SELECT 'A' || 'B' FROM DUAL -- SELECT CONCAT('A', 'B') FROM DUAL	'AB'
數值	1	2	SELECT CAST(1 AS CHAR(1)) + CAST(2 AS CHAR(1)) --數值必需先轉型 SELECT CONCAT(1, 2)	SELECT 1||2 --可不用先轉型 FROM DUAL -- SELECT CONCAT(1, 2) FROM DUAL	'12'

2. 取得部分字串

取得字串指定運算式中的特定段落。

MSSQL提供SUBSTRING、LEFT及RIGHT等三種函數。

ORACLE提供SUBSTR、SUBSTRB函數。

SUBSTR(ING)函數: 取得字串(str)從起始位置(st)起指定長度(len)之子字串。

	語法	說明
S	SUBSTRING(str, st, len)	- 長度(len)為必要參數，不允許負值。
O	SUBSTR(str, st [,len])	- 長度(len)為選擇參數，省略則代表取得完整字串。 - 起始位置(st): 負值代表從字尾起算(自右而左)。

函數功能對照

功能	MSSQL	ORACLE
取出子字串	SUBSTRING(str, st ,len)	SUBSTR(str, start [,len])
取出字串左側子字串	LEFT(str, len) SUBSTRING(str, 1, len)	SUBSTR(str, 1, len)
取出字串右側子字串	RIGHT(str, len)	SUBSTR(str, -st) SUBSTR(str, -st, len)

範例:

MSSQL	ORACLE	結果	說明
SELECT LEFT('ABANDON', 2) , SUBSTRING('ABANDON', 1, 2)	SELECT SUBSTR('ABANDON', 1, 2) FROM DUAL	AB	左側取出2個字元
SELECT SUBSTRING('ABANDON', 5, 8000) 註: 剩餘長度可用字元型態最大長度替代	SELECT SUBSTR('ABANDON', 5) FROM DUAL	DON	由第5碼取至字尾
SELECT RIGHT('ABANDON', 2)	SELECT SUBSTR('ABANDON', -2) FROM DUAL	ON	右側取出2個字元
無法直接對應 SELECT LEFT(RIGHT('ABANDON', 2), 1)	SELECT SUBSTR('ABANDON', -2, 1) FROM DUAL	O

3. 字串取代

以新字串取代目標字串中的特定（舊）字串。MSSQL及ORACLE均提供REPLACE函數，但使用上有部分差異，語法如下：

	語法	說明
S	REPLACE(str, search_str, replacement_str)
O	REPLACE(str, search_str [,replacement_str])	replacement_str預設為空字串

註：REPLACE(string, 舊字串, 新字串)

MSSQL的STUFF及ORACLE的TRANSLATE較少用，後續再討論。

4. 字串位置

字串中搜尋特定子字串所出現位置。

ORACLE: 提供ISNTR函數

MSSQL:  提供CHARINDEX函數，另提供具有『類似』正則表示式之PATINDEX函數。語法整理及

說明如下：

	語法	說明
S	CHARINDEX(set, str, [, st])	- 字串(str)與子字串(set)參數位置與一般習慣不同 -
O	INSTR(str, set, [, st[ ,occurrence]])	提供出現次數的指定。 起始位置可為負值，代表自右而左搜尋。

參數位置

MSSQL所提供CHARINDX與ORACLE的INSTR函數差異如反白部分，字串中搜尋特定的子字串所出現位置，此兩個主要必要參數對於兩種資料庫參數順序不同。

起始位置（st）

兩者均可選擇指定搜尋起始位置（st），若無指定搜尋起始位置，則採用預設值其值為1，ORACLE的起始位置設為負值時，代表由右向左搜尋。

ORACLE的INSTR除起始位置為選擇性參數外，另外一個選項為出現的次數（Occurrence），子字串可能於字串中出現多次，由此選項可指定搜尋出第幾次的位置。

5. 字串填補

在字串左側或右側以特定字元填補至特定長度。ORACLE提供RPAD及LPAD函數，而MSSQL並未提供類似函數，僅就類似功能的SPACE及REPLICATE兩個函數。

MSSQL

語法	說明
REPLICATE (str, count)	傳回重複產生特定字元。
SPACE(count)	傳回特定長度空白字元，為 REPLICATE函數特例。

產生五個「0」字元的字串，SQL語法如下所示，結果為00000。

SELECT REPLICATE('0', 5), SPACE(5)

ORACLE

可指定於在字串左側或右側以特定字元填補至特定長度。其語法如下：

語法	說明
LPAD(str, length [,set])	傳回字串算第m位置起n個字元
RPAD(str , length [,set])	傳回字串左側算起前第n個字元

LPAD函數可將欄位的左側填補（pad）任何字元集。若未給定內容，則此函數將自動以空白來填補，預設值為空白。以下以數字字串123為例，將以字元0補足5位，SQL語法及結果如下：

SQL	結果
SELECT LPAD('123', 5, '0') FROM DUAL	00123

6. 字串長度

ORACLE提供LENGTH()，MSSQL則提供LEN()及DATALENGTH()函數，語法如下所示：

資料庫	語法	說明
MSSQL	LEN(str)	忽略字串後方的空白字元
	DATALENGTH(str)	任何資料型態長度，空白也計入
ORACLE	LENGTH(str)	字元數(Charater)
	LENGTHB(str)	位元數(Byte)

請特別注意，MSSQL的LEN()函數是傳回忽略（剔除）後方空白字元的字元數，DATALENGTH則仍計入右方空白字完。

但於MSSQL其他字串函數或ORACLE的LENGTH()函數，仍視字串後方的空白為有效字元。執行結果如下表：

測試	MSSQL	ORACLE	結果
'ABILITY'	SELECT LEN('ABILITY')	SELECT LENGTH('ABILITY') FROM DUAL	7
'ABILITY□'	SELECT LEN('ABILITY□')	SELECT LENGTH('ABILITY□') FROM DUAL	MSSQL→7 ORACLE→8

7. 刪除空白（特定字元）

若需移除字串前後空白，MSSQL提供RTRIM()及LTRIM()兩個函數，LTRIM()函數將移除字串前面（或稱左側）的空白字元，RTRIM()函數則將移除字串後方（或稱右側）的空白字元；ORACLE除了提供與MSSQL相同指令外（ORACLE相對應函數的功能則較多），另外提供可同時移除前後空白字元的TRIM()函數。

測試	MSSQL	Oracle	結果
左側空白	LTRIM('□□ABANDON□□')	LTRIM('□□ABANDON□□')	ABANDON□□
右側空白	RTRIM('□□ABANDON□□')	RTRIM('□□ABANDON□□')	□□ABANDON
左、右側空白	無直接對應，但可用 RTRIM(LTRIM('□□ABANDON□□'))	TRIM('□□ABANDON□□')	ABANDON