[MSSQL] STUFF字串置換函數使用方法及範圍

STUFF以替換字串(replace_str)置入原字串(str)中。語法如下:

STUFF ( str , start , length , replace_str )

原字串（str）所設定之開始位置（start）起移除指定長度（length）之字元，再將替換字串（replace_str）置入原字串（str）的開始位置（start）中。可將前述說明，整理如下：

STUFF(原字串, 起始位置, 移除長度, 替換字串)

以下將針對『20130827999』交易序號（TRAN_No）以STUFF函數進行進行升/減碼等運算，說明此函數之用途。交易序號是以年月日加上3碼流水號（YYYYMMDD999）所組成，為清楚得知字串各位置/值，將『20130827999』下表 

位置	1	2	3	4	5	6	7	8	9	10	11
值	2	0	1	3	0	8	2	7	9	9	9

SQL:

SELECT Tran_No

     , STUFF(Tran_No, 9, 0, '0')    "升碼"

     , STUFF(Tran_No, 7, 2, '')      "減碼(中)"

     , STUFF(Tran_No, 1, 8, '')      "減碼(前)"

     , STUFF(Tran_No, 9, 100, '')   "減碼(後)"

     , STUFF(Tran_No, 7, 2, '27')    "置換"

FROM

     (

     SELECT '20130827999' Tran_No

     ) A

執行結果:

說明:

功能	SQL	結果	說明
升碼	STUFF(Tran_No, 9, 0, '0')	20130827999 Ä201308270999	由位置9，刪除0個字元，置入『0』。
減碼 （中間）	STUFF(Tran_No, 7, 2, '')	20130827999 Ä201308999	由位置7，刪除2個字元，置入空字串。將日期中的日 (即27)剔除。
減碼 （前面）	STUFF(Tran_No, 1, 8, '')	20130827999 Ä999	由位置1，刪除8個字元，置入空字串。將日期 (即20130827)剔除。
減碼 （前面）	STUFF(Tran_No, 9, 100, '')	20130827999 Ä20130827	由位置9，刪除100個字元，置入空字串。將序號(即999)剔除。刪除字元數無須計算，用稍大一點的數值即可，建議也可直接用8000（varchar上限）。
置換	STUFF(Tran_No, 7, 2, '28')	20130827999 Ä20130828999	由位置7，刪除2個字元，置換為28。序號中日期由27日修改為28日。

STUFF函數主要用途如下

1.    升碼（置入）

在原字串中，於指定位置中將置入特定字串以進行升碼。可應用於交易代碼長度不足，進行升碼。

2.    減碼（移除）

在原字串中，於指定位置中移除特定長度字元。可應用於以XML PATH組合CSV字串中使用，以移除組合CSV字串時於第1碼或最後1碼所增加而多餘之逗號（,）字元。

3.    置換（取代）

在原字串中，於指定位置中移除特定長度字元，並置入替換字串。可應用於置換指定位置上之特定字元。

最後，部分情況下STUFF函數與REPLACE函數用途有點類似，後續再探討兩者之異同點。