Padding identity column values with zeros

Hi Guys

Is there an easy way to pad identity column values with zeros to the left (0001, 0010,0100, etc) or I will just have to format them before displaying

George

This works for me a lot of times.

REPLICATE('0', 4-len(cast(id as char(4))) + cast(id as char(4))

|||

You could use an expression like:

right(replicate('0', 8) + cast($identity as varchar), 8)

-- or

right(replicate('0', 8) + cast(identitycol as varchar), 8)

You could either use a computed column or a view with the expression. Alternatively, you could simply do these type of operations on the client side.

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

Padding an auto-incremented Int column to ten zeros

Good day gentleman,
Is it possible to pad columns of Int data type with zeros?

I currently have a primary key column of data type Int which is auto-incremented by 1. My problem is I need to pad the auto-incremented ID's with ten zeros so, 0000000009 instead of 9.

I can pad the ID's in the c# dataaccess layer, but i need to generate/store them padded in the database to allow direct reporting.

I am using Enterprise Manager and i can see a Formula field when the table is in design view, can i use this? i am just guessing here.

Any comments or pointers would be a big help.What you are discussing is a display issue. An int is, by definition, isolated from any particular display formatting. It is just a number. You could add an additional column (nvarchar) and have a trigger on inserts that populates that nvarchar() column with a string value with 0 padding.