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 and Writing to a fixed format flat file!

Hi,

I am trying to write to a fixed format flat file using Flat File Destination Data Flow Component. I have all required information gathered from more than one sources. But when I tried to format the columns to a big string that will make up one line in the flat file, I could not figure out how to do that. Couple of issues that I am facing are:

How to padd different columns? For example, One interger column has could be 1 to 10 character long in my case. When I convert to string, dont know how to padd the remaining characters i.e. if the value of integer is '1234', it should be written to file as '1234 ' . Which transformation is best in this case, if available? How to convert T-SQL datetime to a specific date and time format to write in the flate file? I have to write these date formats depending upon one of the parameters passed. Also, I dont want to put a delimiter at the end of each column, just the new line characters at the end of each record. Some of the columns has some unwanted characters (like new line characters) how to find them and remove them from the string. Can we directly write columns to a specific position in the flat file? e.g. col 1 a position 1 and col2 starts at postion 20 etc.

Your co-operation will be appreciated.

Thanks,

Paraclete

Take a look at a ragged-right or fixed width flat file connection manager. WHen you create the connection manager, you can choose ragged right or fixed width instead of delimited. This should solve a number of your issues.sql

Package.dtsx -> XML format description/manual request

Hi All,

As I see it is possible to view a package (dtsx) in XML form.

Is the XML stable and documented? Would it be good decision to

use the XML as source for third party development or we need to use

some API to get package's content ? Where I could get the package's XML

structure description/manual ?

Thanks,

Sergiy

I'd say the overall schema is stable, but it is not publically documented so subject to change I guess. What will change is the detailed sections within tasks or components that describe properties. Task or component developers can change that whenever they feel like, and may do. Whilst that may sound a bit dangerous, it is perfectly acceptable since they would be expected to write the appropriate code to upgrade between component versions, or even downgrade gracefully if required, it is possible.

The safest way would be to use the API/object model, but XML may be sufficient for what you need. Just think what will happen if a new element or attribute appears because a new version of a task comes out and it has a new property. Examples of this are some of my own developed tasks, or changes MS made in SP1.