CODEWITHSUNDEEP
sql-server-2005
Ashish Sharma
Ashish Sharma

Reputation: 875

CONVERT function in SQL Server 2005

Can we do following in SQL Server 2005 query with convert function.

MaxRegID =  (1).ToString("D5"); // c# code
MaxRegID = 00001

convert (1) number with 5 digits in column

Upvotes: 1

Views: 1124

Answers (1)

marc_s
marc_s

Reputation: 755321

No - right now, SQL Server doesn't have an equivalent to the Format (used in .ToString()) in .NET.

You can achieve this with:

SELECT RIGHT('00000' + CAST(1 AS VARCHAR(5)), 5)

SQL Server 2012 ("Denali") will introduce a FORMAT T-SQL function

You could also create a general purpose function for this - something like:

CREATE FUNCTION dbo.PadInt(@input INT, @length INT)
RETURNS VARCHAR(20)
AS BEGIN
    DECLARE @Result VARCHAR(20)

    DECLARE @Padding VARCHAR(20) 
    SELECT @Padding = REPLICATE('0', @Length)

    SELECT @Result = RIGHT(@Padding + CAST(@Input AS VARCHAR(20)), @Length)
    RETURN @Result
END

and call it like this:

SELECT dbo.PadInt(1, 5)

and get back:

00001

Upvotes: 1

Related Questions