T-SQL使用案例——結果資料前面自動補0

發糞塗牆發表於2012-06-27


現象:

在開發的過程中,往往需要數字和字串互轉。在轉換的過程中,可能需要把1程式設計00001,這樣的格式。實現這種樣子是有非常多的方法,本文主要提供一種自定義函式來實現

 

說明:

         一般來說這種處理主要使用CONVERT或者CAST來實現即可。思路是先轉換為字串然後在不上足夠數量的0,最後使用RIGHT函式,從右邊取出需要長度。

 

案例:

         本例中使用AdventureWork資料庫作為例子。

首先,執行下面語句:

--查詢部門資料

SELECT DepartmentID,Name

FROM AdventureWorks.HumanResources.Department

 

結果如下:

 

然後,使用CAST將原來部門編號轉換成VARCHAR()字串。接著加上固定數量的0,然後利用RIGHT函式,從右邊去除指定數量的字串。

程式碼如下:

--查詢部門資料並將數字根據位數補上0

SELECT RIGHT(REPLICATE('0',5)+CAST(DepartmentID AS varchar(10)),5) AS 'DepartmentID',Name

FROM AdventureWorks.HumanResources.Department

ORDER BY DepartmentID

 

結果:

以上的是準備工作,現在開始編寫一個自定義函式來實現,此函式有兩個引數,第一個引數是要補0或者其他數目的個數,第二個是要轉換的數字。最後把結果以字串方式返回:

 

函式程式碼:

CREATE FUNCTION fnZero

(

    @n INT,

    @i INT

)

RETURNS VARCHAR(125)

AS

BEGIN

    RETURN

    (

        RIGHT(REPLICATE('0',@n)+CAST(@i as varchar(125)),@n)

    )

END

 

使用例子:

--查詢部門資料並將數字根據位數補上0

SELECT DepartmentID as 'DepartmentID',dbo.fnZero(5,DepartmentID) as 'DepartmentID'

FROM AdventureWorks.HumanResources.Department

ORDER BY 1

注意,由於兩個列都是同名,所以ORDER BY 那裡使用了1,而不是列名.

結果如下:

 

當然,也可以改進該函式,使得函式能滿足很多要求.這部分請讀者自己去研究了.

 

 

相關文章