Prefix '0' with Numbers

PandianS
Posted by PandianS under Sql Server category on | Points: 40 | Views : 2481
In most of the scenario, we need to prefix '0' (Zero) with numbers.

If my result is 15 then, it should be resulted as 00015
If my result is 2010 then, it should be resulted as 02010

So, the total length should be 5.

If the total length is 5 then '0' should be prefixed based on the result.

Input Result
1 00001
15 00015
151 00151
1515 01515
15151 15151

IF OBJECT_ID('[DBO].[UFN_PrefixZero]','FN') IS NOT NULL
DROP FUNCTION [DBO].[UFN_PrefixZero]
GO
CREATE FUNCTION [DBO].[UFN_PrefixZero](@Input VARCHAR(10),@TotalLength INT) RETURNS VARCHAR(MAX) AS
BEGIN
RETURN ISNULL(REPLICATE('0',@TotalLength - LEN(@Input)),'') + @Input
END
GO

Input is 45, Total length is 5
SELECT [DBO].UFN_PrefixZero(45,5)
GO
Result Is: 00045

Input is 45, Total length is 10
SELECT [DBO].UFN_PrefixZero(45,10)
GO
Result Is: 0000000045

Input is 45, Total length is 20
SELECT [DBO].UFN_PrefixZero(45,20)
GO
Result Is: 00000000000000000045

Input is 45, Total length is 50
SELECT [DBO].UFN_PrefixZero(45,50)
GO
Result Is: 00000000000000000000000000000000000000000000000045

Comments or Responses

Login to post response