2013年8月28日 星期三

常用字串函數

#
功能
SQL SERVER
Oracle
說明
1
字串連結
+CONCAT
||CONCAT
字串連結。
S: 使用加號(+),2012版本起支援CONCAT函數。
O: 使用||CONCAT函數。
2
取出子字串
SUBSTRING
LEFT
RIGHT
SUBSTR
取得字串指定算式中的特定段落。
O: 所提供SUBSTR可指定起始位置由左或右起算,並取得特定長度。
S: 則提供三種擷取部分字串的函數。
3
字串取代
REPLACE
STUFF
REPALACE
TRANSLATE
以新字串取代目標字串中的特定(舊)字串。
4
字串位置
CHARINDEX
PATINDEX
INSTR
搜尋字串中特定字串的開始位置
5
字串填補
SPACE

LPAD
RPAD
在字串左側或右側以特定字元填補至特定長度
6
字串長度
LEN
DATALENGTH
LENGTH
LENGTHB
取得字串中字元數量
7
刪除空白
(特定字元)
RTRIM
LTRIM
RTRIM
LTRIM
TRIM
刪除字串前後空白(O尚支援特定字元)
S: 提供RTRIM()LTRIM()兩個函數。
O: 除提供與相同指令外(O除空白外,尚支援特定字元),尚支援可同時移除前後空白字元的TRIM()函數。
8
字串大小寫
LOWER
UPPER
LOWER
UPPER
INITCAP
LOWER將字串中所有字元轉換為小寫字元。
UPPER將字串中所有字元轉換為大寫字元。
INITCAP第一個字元轉換為大寫。
註:MSSQL 2012版本起支援CONCAT函數

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 1||2 --可不用先轉型
FROM DUAL
--
SELECT CONCAT(1, 2)
FROM DUAL
'12'

2. 取出子字串
取得字串指定算式中的特定段落,SQL SERVER提供SUBSTRINGLEFTRIGHT等三種函數。ORACLE提供SUBSTR函數。
SUBSTR(ING)函數比較
資料庫
語法
說明
MSSQL
SUBSTRING(str, start, length)
長度為必要參數
ORACLE
SUBSTR(str, start [,length])
長度為選擇參數,預設值為1,若負值代表自右而左。

函數功能對照
功能
MSSQL
ORACLE
取出子字串
SUBSTRING(str, start ,length)
SUBSTR(str, start [,length])
取出字串左側子字串
LEFT(str, length)
SUBSTRING(str, 1, length)
SUBSTR(str, 1, length)
取出字串右側子字串
RIGHT(str, length)
SUBSTR(str, -start)
SUBSTR(str, -start, length)

3. 字串取代
以新字串取代目標字串中的特定(舊)字串。MSSQLORACLE均提供REPLACE函數,但使用上有部分差異,語法如下:
資料庫
語法
說明
MSSQL
REPLACE(str, search_str, replacement_str)

ORACLE
REPLACE(str, search_str [,replacement_str])
replacement_str預設為空字串
REPLACE(string, 舊字串, 新字串)
MSSQLSTUFFORACLETRANSLATE較少用,後續再討論。

4. 字串位置
字串中搜尋特定子字串所出現位置。ORACLE提供ISNTR函數,SQL SERVER提供CHARINDEX函數,另外提供具有『類似』正則表示式功能的PATINDEX函數。語法整理及說明如下:
資料庫
語法
說明
MSSQL
CHARINDEX(set, str, [, start])
字串與子字串參數位置與一般習慣不同
ORACLE
INSTR(str, set, [, start[ ,occurrence]])
提供出現次數的指定。起始位置可為負值,代表自右而左搜尋。
MSSQL所提供CHARINDXORACLEINSTR函數差異如反白部分,字串中搜尋特定的子字串所出現位置,此兩個主要必要參數對於兩種資料庫參數順序不同
兩者均可選擇指定搜尋起始位置(start),若無指定搜尋起始位置,則採用預設值其值為1ORACLE的起始位置設為負值時,代表由右向左搜尋
ORACLEINSTR除起始位置為選擇性參數外,另外一個選項為出現的次數(Occurrence,子字串可能於字串中出現多次,由此選項可指定搜尋出第幾次的位置

5. 字串填補
在字串左側或右側以特定字元填補至特定長度。ORACLE提供RPADLPAD函數,而SQL SERVER並未提供類似函數,僅就類似功能的SPACEREPLICATE兩個函數。
ü MSSQL提供的函數如下:
語法
說明
REPLICATE (str, count)
傳回重複產生特定字元。
SPACE(count)
傳回特定長度空白字元,為 REPLICATE函數特例。
產生五個「0」字元的字串,SQL語法如下所示,結果為00000
SELECT REPLICATE('0', 5), SPACE(5)

ü ORACLE提供RPADLPAD函數
可指定於在字串左側或右側以特定字元填補至特定長度。其語法如下:
語法
說明
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()SQL SERVER則提供LEN()DATALENGTH()函數,語法如下所示:
資料庫
語法
說明
MSSQL
LEN(str)
忽略字串後方的空白字元
DATALENGTH(str)
任何資料型態長度,空白也計入
ORACLE
LENGTH(str)
字元數(Charater)
LENGTHB(str)
位元數(Byte)
請特別注意,MSSQLLEN()函數是傳回忽略(剔除)後方空白字元的字元數,DATALENGTH則仍計入右方空白字完。但於SQL SERVER其他字串函數或ORACLELENGTH()函數,仍視字串後方的空白為有效字元。執行結果如下表:
測試
SQL SERVER
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除了提供與SQL SERVER相同指令外(ORACLE相對應函數的功能則較多),另外提供可同時移除前後空白字元的TRIM()函數
測試
SQL SERVER
Oracle
結果
左側空白
LTRIM('ŸŸABANDONŸŸ')
LTRIM('ŸŸABANDONŸŸ')
ABANDONŸŸ
右側空白
RTRIM('ŸŸABANDONŸŸ')
RTRIM('ŸŸABANDONŸŸ')
ŸŸABANDON
左、右側空白
無直接對應,但可用
RTRIM(LTRIM('ŸŸABANDONŸŸ'))
TRIM('ŸŸABANDONŸŸ')
ABANDON

沒有留言:

張貼留言