Padding fields with zeros.

I am trying to create a field in SQL query builder that takes the input number 12345 and formats it 0000012345. In MSAccess there is a great Format() function which allowed this to be done easily, but I cant find an equivalent in SQLServer. Can anyone provide me with a new function and possibly a sample? Thanks in Advance,

Michael.

try this

SELECT cast(replace(str(12345 ,10),' ','0') AS char(10))

OR write Stored Procedure as you need ( i think)

CREATE FUNCTION fnPadLeft

( @.PadChar char(1), @.PadToLen int, @.BaseString varchar(100) )

RETURNS varchar(1000)

AS

BEGIN

DECLARE @.Padded varchar(1000)

DECLARE @.BaseLen int

SET @.BaseLen = LEN(@.BaseString)

IF @.BaseLen >= @.PadToLen

BEGIN

SET @.Padded = @.BaseString

END

ELSE

BEGIN

SET @.Padded = REPLICATE(@.PadChar, @.PadToLen - @.BaseLen) + @.BaseString

END

RETURN @.Padded

END

Now try this

SELECT dbo.fnPadLeft('0', 10, 12345) ==> 0000012345

Parameters:

First = Padding character

Second = Total Length

Third = Your Column

Best of Luck

Regards,

Thanks.

Gurpreet S .Gill

|||

You can also try this

select Replicate('0',10-Len(Column1))+ Convert(Varchar,Column1) from Table1

I am assuming Column1 in the table is INT. If it is varchar, no need to for conversion.

|||

SELECT RIGHT('0000000000' + CONVERT(VARCHAR(10),col1) , 10) FROM table1

|||

Thanks all! I will give it a shot...

Michael