T-SQL 將數字前面補0

在整理過去的程式時，看到 N 年前自己曾經為了解決將數字前面補0的問題，寫過如下程式(四位數，不足補0)：

declare @no_x char(4);

set @no_x='33'

if(@no_x is null )
begin
 set @no_x=0
end
 set @no_x =  CONVERT(char(4), (@no_x) )

if(len(@no_x)=1)
begin
 set @no_x ='000'+ @no_x
end

if(len(@no_x)=2)
begin
 set @no_x ='00'+ @no_x
end

if(len(@no_x)=3)
begin
 set @no_x ='0'+ @no_x
end

select @no_x

回想起當初真是不夠精進，現在已經有比較簡潔的方法了，大致上舉兩個例子：一個是使用 POWER + RIGHT，另一個則是使用 REPLICATE。

POWER + RIGHT
POWER() 函數是用來計算並取得數值的 N 次方值。使用的目的，是希望取得類似 10000的結果，接著與我們原先的數值相加，最後利用 RIGHT 由右邊開始取值來完成。

declare @id int
set @id=33
select RIGHT(POWER(10,4)+@id,4)

REPLICATE
REPLICATE()函數是將字串值重複指定的次數。可以透過 LEN() 取得目前數值長度，並將尚可使用的長度帶入REPLICATE()函數。

declare @id int
set @id=33
select REPLICATE('0',4-len(@id))+ CONVERT(nvarchar(4),@id)

如果是針對整個資料表作處理，則可參考以下程式：

create table #tmp (id bigint identity(1,1),[name] nvarchar(20))
insert into #tmp ([name]) values ('paladin')
insert into #tmp ([name]) values ('hugo')
insert into #tmp ([name]) values ('ken')

--使用 REPLICATE
select REPLICATE('0',4-len(id))+ CONVERT(nvarchar(4),id) as new_id,name from #tmp
--使用 POWER
select right(POWER(10,4)+id,4) as new_id,name from #tmp


drop table #tmp

參考：
01：http://webdesign.kerthis.com/sql/sql_function_power (power)
02：http://msdn.microsoft.com/zh-tw/library/ms174383(v=SQL.105).aspx (REPLICATE)
03：http://gemmarecord.blogspot.com/2008/07/sql-0.html (Ref. Gemma)
04：http://shihshu.blogspot.com/2009/06/stored-procedure0.html (Ref. Ken)