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
GOInput 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