padding variables

I know this may be an elementary question but can anyone tell me how to pad a
variable.
For example
@.chvcode has value ��123�� and I want to pad it with ��00123��.
Logger
Wil your variable always be an int, if so you can do
SET @.chvcode = RIGHT(100000 + @.chvcode,5)
Andy
"Logger" wrote:

> I know this may be an elementary question but can anyone tell me how to pad a
> variable.
> For example
> @.chvcode has value ��123�� and I want to pad it with ��00123��.
>
sql

padding variables

I know this may be an elementary question but can anyone tell me how to pad
a
variable.
For example
@.chvcode has value ��123�� and I want to pad it with ��00123��.Logger
Wil your variable always be an int, if so you can do
SET @.chvcode = RIGHT(100000 + @.chvcode,5)
Andy
"Logger" wrote:

> I know this may be an elementary question but can anyone tell me how to pa
d a
> variable.
> For example
> @.chvcode has value ��123�� and I want to pad it with ��00123��.
>

padding variables

I know this may be an elementary question but can anyone tell me how to pad a
variable.
For example
@.chvcode has value â'123â' and I want to pad it with â'00123â'.Logger
Wil your variable always be an int, if so you can do
SET @.chvcode = RIGHT(100000 + @.chvcode,5)
Andy
"Logger" wrote:
> I know this may be an elementary question but can anyone tell me how to pad a
> variable.
> For example
> @.chvcode has value â'123â' and I want to pad it with â'00123â'.
>

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

Pad Trailing Spaces in a Report Expression

I am trying to pad a fixed number of trailing spaces into a report expression, as follows:

Data:

CUSTOMER_NAME
-
Michael
Peter
John

Result (e.g. with 10 spaces padded, assuming all names are below 10 characters long):

"Michael "
"Peter "
"John "

Is there an easy way to achieve this ?

Thanks.

Kenny

If you want a 10 character string, right padded with spaces, you could do this:

=Fields!datafield.Value & right(" ", 10 - len(Fields!datafield.Value))

(10 spaces in quotes)

HTH

BobP

pad the generated flat file with 0

Hi Guys,

Related to my last questions on SSIS work i'm doing, Is there a way to pad 0 on my generated flat file dynamically.

I'm getting the data from 1 table and then generating the file. The file i need to generate would have data at the desired location as the file is being used by another system.

Depedning on the data I want to put the padding of "0" and "3" inc certain fields. How am i suppose to do it.

Apart from this I would need to megre 2 or more column and before the merge do an airthmatic operation.

What would be the best component to use script component or derived column?

Ta

Gemma

Gemma,

Not sure if this will help or not, but it is how i've padded zero's before...

Use a derived column:

@.LENGTH is the length you would like to pad to)

@.MyStr is the character expression or string you would like to pad

SUBSTRING(REPLICATE("0", @.LENGTH), 1, (@.LENGTH + 1) - LEN(TRIM(@.MyStr))) + TRIM(@.MyStr)

|||

Here's another - using the same variables as above:

RIGHT(REPLICATE("0",@.Length) + @.MyStr, @.Length)

|||

Hi Guys,

I don't know whether this is completely what I asked.

I would need to pad the values dynamically. By this I mean the padding would be done depending on the actual length of data in the columns. So when the file is generated I would have to check each column and if certain column's data length is less then fixed length then pad it with zero.

Other then that how would I suppose to use these variables?

That is why I asked Script or derived column which one.

Just a quick note, the file I'm generating is going to be fixed width as i guess it and i'm generating a dummy file but with fixed width it's not putting the records in each line. Instead file is having all the records at the same line. Why? I've already checked. There are 2 columns which i'm writing. The first i'm putting in as 20 and the 2nd one as 35 width still i'm getting this problem.

Ta

Gamma

|||

The above expressions do exactly what you asked. The variables are simply placeholders in the code. If you know that your column is 20 wide and you want it to pad out the non filled data to 20 places (to the right) you would simply put your column name in the @.MyStr and 20 in the @.Length.

for example we have a membership number that needs to be padded out to 9 digits. The column's name is Member.

SUBSTRING(REPLICATE("0", 9), 1, (9+ 1) - LEN(TRIM([Member]))) + TRIM([Member])

So, what that does is takes a string of 9 zero's (Replicate("0", 9) and substrings out the 1, to 9 - trimmed column length and then adds the trimmed column information.

i.e. member number of "12345 " would yeild substring(replicate("0", 9), 1, 10 - 5) + "12345" > "0000" + "12345" > "000012345"

You would need to set this up for each column seperately.

hope this helps.

sql

Pad Spaces in FFConnection properties?

This is a considerably LOW priority, but is there a way to padd spaces in ssis flat file connection properties? We have cases where a CSV file will read in and throw a truncation error because the field length is greater then defined. However, the CSV file might look like ,"ABC ", where a number of trailing spaces are found. Is there some way to handle this without having to adjust teh field length, maybe ignore whitespace at the source?There is no way at the Flat File Source to ignore trailing whitespace, however there is a work around. Say in this case your field length is 3, and if there are trailing spaces after that, you want to ignore them. You can then use "Ignore Failure" for Truncation Error Disposition. That is, in the Error Output tab for the Flat File Source, for Truncation of this column, choose "Ignore Failure", instead of "Fail Component". By doing this, it will only get the specified characters, and ignore the rest, whether they are trailing white spaces, or some other data there. You have to make sure that this is not causing you to accept some data which you otherwise wanted to fail on really.|||

That is partially the problem, we only want to ignore whitespaces, but if there is actual chars then we would want to throw truncation error.

Thanks for your thoughts...

pad index and fill factor

We have a new database set up on MS SQL Server 2005. It was noticed that the
pad index has not been set and I have been asked to look into what is a
recommended value.
What is the rule of thumb when setting the pad index and fill factor for
optimum performance?The value for fill factor will depend on what the level of read to write is
upon the relevant index.
For example , if it's going to be a read only then keep the fill factor at 0
as you won't need to allow extra spacing in the pages for new inserts.
On the other hand , if there will be large amounts of inserts on the index ,
then you may want to increase the fill factor above 0 .
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:un3uE4QoGHA.3836@.TK2MSFTNGP04.phx.gbl...
> We have a new database set up on MS SQL Server 2005. It was noticed that
the
> pad index has not been set and I have been asked to look into what is a
> recommended value.
> What is the rule of thumb when setting the pad index and fill factor for
> optimum performance?
>|||Pad index and fill factor are only applied when an index is created,
or rebuilt (DBCC DBREINDEX, ALTER INDEX in 2005). As such they are
not meaningful until you have data in the database. Is your new
database populated yet?
Likewise, you need to plan your schedule for rebuilding the indexes
and fit that into the calculation. If you are rebuilding them weekly
it is probably a mistake to build in enough elbow room for two months
worth of activity. And the reverse would probably be even more
important, not to build in one week of free space if the rebuild
schedule is two months.
The key to setting these well is knowing your data, your tables, and
your indexes. Correct values vary by index, and by how the table is
used. An index on an identity column, where new values are always
added to the end, will not be helped by having free space all through
the index. The same is true of any table where the clustered index
causes inserts to go to the end. Think of an Invoices table,
clustered in InvoiceDate, as an example. A reference table that
changes very slowly might fall in the same category.
On the other hand, an index on the Customer column of the Invoices
table would probably benefit from some free space, as new invoices
will cover the range of customers.
Hope that helps.
Roy Harvey
Beacon Falls, CT
On Thu, 6 Jul 2006 09:08:19 -0600, "Loren Z"
<anonymous@.discussions.microsoft.com> wrote:

>We have a new database set up on MS SQL Server 2005. It was noticed that th
e
>pad index has not been set and I have been asked to look into what is a
>recommended value.
>What is the rule of thumb when setting the pad index and fill factor for
>optimum performance?
>|||One other tidbit to add to these answers about usage.
Just to be clear, "fill factor" represents the percentage to fill the data
page files on index creation or rebuild at the *leaf* layer of the index (if
you have a OLTP system, this is the level were the most expensive
fragmentation occurs).
Pad Index represents the same percentage (in fact, you can't set a different
value for Pad Index than you do for Fill Factor) at the *non-leaf* level of
the index. It's off by default because the cost of fragmentation at that
layer (sometimes referred to as an "unbalanced tree") is usually only
measureable, not visible.
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:un3uE4QoGHA.3836@.TK2MSFTNGP04.phx.gbl...
> We have a new database set up on MS SQL Server 2005. It was noticed that
> the pad index has not been set and I have been asked to look into what is
> a recommended value.
> What is the rule of thumb when setting the pad index and fill factor for
> optimum performance?
>

pad index and fill factor

We have a new database set up on MS SQL Server 2005. It was noticed that the
pad index has not been set and I have been asked to look into what is a
recommended value.
What is the rule of thumb when setting the pad index and fill factor for
optimum performance?The value for fill factor will depend on what the level of read to write is
upon the relevant index.
For example , if it's going to be a read only then keep the fill factor at 0
as you won't need to allow extra spacing in the pages for new inserts.
On the other hand , if there will be large amounts of inserts on the index ,
then you may want to increase the fill factor above 0 .
--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:un3uE4QoGHA.3836@.TK2MSFTNGP04.phx.gbl...
> We have a new database set up on MS SQL Server 2005. It was noticed that
the
> pad index has not been set and I have been asked to look into what is a
> recommended value.
> What is the rule of thumb when setting the pad index and fill factor for
> optimum performance?
>|||Pad index and fill factor are only applied when an index is created,
or rebuilt (DBCC DBREINDEX, ALTER INDEX in 2005). As such they are
not meaningful until you have data in the database. Is your new
database populated yet?
Likewise, you need to plan your schedule for rebuilding the indexes
and fit that into the calculation. If you are rebuilding them weekly
it is probably a mistake to build in enough elbow room for two months
worth of activity. And the reverse would probably be even more
important, not to build in one week of free space if the rebuild
schedule is two months.
The key to setting these well is knowing your data, your tables, and
your indexes. Correct values vary by index, and by how the table is
used. An index on an identity column, where new values are always
added to the end, will not be helped by having free space all through
the index. The same is true of any table where the clustered index
causes inserts to go to the end. Think of an Invoices table,
clustered in InvoiceDate, as an example. A reference table that
changes very slowly might fall in the same category.
On the other hand, an index on the Customer column of the Invoices
table would probably benefit from some free space, as new invoices
will cover the range of customers.
Hope that helps.
Roy Harvey
Beacon Falls, CT
On Thu, 6 Jul 2006 09:08:19 -0600, "Loren Z"
<anonymous@.discussions.microsoft.com> wrote:
>We have a new database set up on MS SQL Server 2005. It was noticed that the
>pad index has not been set and I have been asked to look into what is a
>recommended value.
>What is the rule of thumb when setting the pad index and fill factor for
>optimum performance?
>|||One other tidbit to add to these answers about usage.
Just to be clear, "fill factor" represents the percentage to fill the data
page files on index creation or rebuild at the *leaf* layer of the index (if
you have a OLTP system, this is the level were the most expensive
fragmentation occurs).
Pad Index represents the same percentage (in fact, you can't set a different
value for Pad Index than you do for Fill Factor) at the *non-leaf* level of
the index. It's off by default because the cost of fragmentation at that
layer (sometimes referred to as an "unbalanced tree") is usually only
measureable, not visible.
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:un3uE4QoGHA.3836@.TK2MSFTNGP04.phx.gbl...
> We have a new database set up on MS SQL Server 2005. It was noticed that
> the pad index has not been set and I have been asked to look into what is
> a recommended value.
> What is the rule of thumb when setting the pad index and fill factor for
> optimum performance?
>

PAD function in Data Tranformation Editor

I have a value with a float datatype in Excel that is actually a date. For example, the value displays 1272006 for January 27, 2006. I am trying to convert this value to a date. I can do this in several stages using T-SQL by converting the datatype to integer, using replicate to make it eight characters and than using substrings to make it look like 01-27-2006 which SQL Server will recognize as a date.

My problem is that there is nothing similar to a PAD (replicate) function using the Data Tranformation Editor in Integration Services. There is a replicate function but it merely functions to repeat a value. It doesn't appear to perform the same PAD function as the T-SQL replicate does. Here is my formula in T-SQL that works okay.

right(replicate('0', 8) + convert(varchar, cast([Date] as varchar)), 8)

I could do this by creating a staging table but I want to handle the entire transformation process within Integration Services. Can anyone offer me some advice? Thank you.

David

I don't think we have your full statement... I'm not seeing where you convert to a date...

Never-the-less, why not substring the "date" field and then cast it to DT_DBDATETIME?|||Question, how is Jan 1st, 2006, represented?

112006? Or 1012006?|||1012006|||

I'm not able to use the substring because the values varies between seven and eight characters. For example, January 1, 2006 is 1012006 while October 1, 2006 is 10012006. I figure I need to convert the value to eight characters by padding a zero on the left. I then can use a substring to convert it to a SQL Server recognizable date like 01-01-2006.

David

|||

davidg12 wrote:

I'm not able to use the substring because the values varies between seven and eight characters. For example, January 1, 2006 is 1012006 while October 1, 2006 is 10012006. I figure I need to convert the value to eight characters by padding a zero on the left. I then can use a substring to convert it to a SQL Server recognizable date like 01-01-2006.

David

Okay, so then this should be easy... If you want to do this in one derived column (are you guaranteed that the value is a correct and verified date?), then you can do something like:

(DT_DBTIMESTAMP)(SUBSTRING(RIGHT("0" + datefield,8),5,4) + "-" + SUBSTRING(RIGHT("0" + datefield,8),1,2) + "-" + SUBSTRING(RIGHT("0" + datefield,8),3,2) + " 00:00:00")

In an upstream derived column, you could do the "RIGHT("0" + datefield,8)" calculation, and then just use that field in the above expression. It would make it easier to read.
|||

Thank you Phil. This works great.

David

PAD and Left justify in T SQL

Hi,
Is it possible to PAD and justify output from a select statement? I have to
build an input record from two fields in a table that I am concatenating
together. The result is then going to be passed as a parameter value to a
dll. The dll is looking for input of the form
Field Name position Length
record type 1 1
ClaimID 2 17
some of the ClaimID's are less than 17 characters in length
typical output (an underscore here denotes a space)
Record # Data
1 C_______1234567890
so I need to pad the '1234567890' value with 7 spaces to the left.
At the moment I am doing this after the SELECT from SQL Server but I
wondered if I could do this in the actual SELECT itself?
Any help appreciatedYes, but it is also possible to do this in the DLL or elsewhere in the
presentation area.
(As a demonstration):
SELECT 'C' + RIGHT(REPLICATE('_', 17) + RTRIM(1234567890), 17)
UNION ALL
SELECT 'C' + RIGHT(REPLICATE('_', 17) + RTRIM(1565), 17)
UNION ALL
SELECT 'C' + RIGHT(REPLICATE('_', 17) + RTRIM(12345678901234567), 17)
(In your query):
SELECT 'C' + RIGHT(REPLICATE('_', 17) + RTRIM(ClaimID), 17)
So, as long as claimID is never more than 17 characters, this will work. If
you exceed 17, you will trim off the leading character(s).
A
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
> Hi,
> Is it possible to PAD and justify output from a select statement? I have
> to
> build an input record from two fields in a table that I am concatenating
> together. The result is then going to be passed as a parameter value to a
> dll. The dll is looking for input of the form
> Field Name position Length
> record type 1 1
> ClaimID 2 17
> some of the ClaimID's are less than 17 characters in length
> typical output (an underscore here denotes a space)
> Record # Data
> 1 C_______1234567890
> so I need to pad the '1234567890' value with 7 spaces to the left.
> At the moment I am doing this after the SELECT from SQL Server but I
> wondered if I could do this in the actual SELECT itself?
> Any help appreciated
>|||Joe,
Is this what you're asking for:?
DECLARE @.VAR VARCHAR(50)
SET @.VAR = '1234567890'
SELECT REPLICATE(' ',7) + @.VAR AS 'NEW VALUE'
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
> Hi,
> Is it possible to PAD and justify output from a select statement? I have
> to
> build an input record from two fields in a table that I am concatenating
> together. The result is then going to be passed as a parameter value to a
> dll. The dll is looking for input of the form
> Field Name position Length
> record type 1 1
> ClaimID 2 17
> some of the ClaimID's are less than 17 characters in length
> typical output (an underscore here denotes a space)
> Record # Data
> 1 C_______1234567890
> so I need to pad the '1234567890' value with 7 spaces to the left.
> At the moment I am doing this after the SELECT from SQL Server but I
> wondered if I could do this in the actual SELECT itself?
> Any help appreciated
>|||Or...if char:
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
> Hi,
> Is it possible to PAD and justify output from a select statement? I have
> to
> build an input record from two fields in a table that I am concatenating
> together. The result is then going to be passed as a parameter value to a
> dll. The dll is looking for input of the form
> Field Name position Length
> record type 1 1
> ClaimID 2 17
> some of the ClaimID's are less than 17 characters in length
> typical output (an underscore here denotes a space)
> Record # Data
> 1 C_______1234567890
> so I need to pad the '1234567890' value with 7 spaces to the left.
> At the moment I am doing this after the SELECT from SQL Server but I
> wondered if I could do this in the actual SELECT itself?
> Any help appreciated
>|||Or...if char:
DECLARE @.VAR CHAR(50)
SET @.VAR = '1234567890'
SELECT RTRIM(REPLICATE(' ',7) + @.VAR) AS 'NEW VALUE'
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
> Hi,
> Is it possible to PAD and justify output from a select statement? I have
> to
> build an input record from two fields in a table that I am concatenating
> together. The result is then going to be passed as a parameter value to a
> dll. The dll is looking for input of the form
> Field Name position Length
> record type 1 1
> ClaimID 2 17
> some of the ClaimID's are less than 17 characters in length
> typical output (an underscore here denotes a space)
> Record # Data
> 1 C_______1234567890
> so I need to pad the '1234567890' value with 7 spaces to the left.
> At the moment I am doing this after the SELECT from SQL Server but I
> wondered if I could do this in the actual SELECT itself?
> Any help appreciated
>|||This should get you going. Add 17 spaces to right of the ClaimID, and then k
eep the 17 right.most
characters:
SELECT c1 + (RIGHT(REPLICATE(' ', 17) + c2, 17))
FROM
(
SELECT 'C' as c1, '1234567890' AS c2
) AS tbl
I prefer doing this stuff in the client app, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
> Hi,
> Is it possible to PAD and justify output from a select statement? I have t
o
> build an input record from two fields in a table that I am concatenating
> together. The result is then going to be passed as a parameter value to a
> dll. The dll is looking for input of the form
> Field Name position Length
> record type 1 1
> ClaimID 2 17
> some of the ClaimID's are less than 17 characters in length
> typical output (an underscore here denotes a space)
> Record # Data
> 1 C_______1234567890
> so I need to pad the '1234567890' value with 7 spaces to the left.
> At the moment I am doing this after the SELECT from SQL Server but I
> wondered if I could do this in the actual SELECT itself?
> Any help appreciated
>|||Thank you for the replies , its working well, without help from you guys I
think I would be out of a job.
"Tibor Karaszi" wrote:

> This should get you going. Add 17 spaces to right of the ClaimID, and then
keep the 17 right.most
> characters:
> SELECT c1 + (RIGHT(REPLICATE(' ', 17) + c2, 17))
> FROM
> (
> SELECT 'C' as c1, '1234567890' AS c2
> ) AS tbl
> I prefer doing this stuff in the client app, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
>|||> Thank you for the replies , its working well, without help from you guys I
> think I would be out of a job.
I've often wondered if there is an easy way to divert portions of salaries
of the people we help, then I wouldn't need a job. :-)|||If you're anything like me you couldn't stop this even if you wanted to or
was rich enough to. We'd get bored ;-)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eOrObWZ2FHA.2604@.TK2MSFTNGP12.phx.gbl...
> I've often wondered if there is an easy way to divert portions of salaries
> of the people we help, then I wouldn't need a job. :-)
>|||rest assured my salary sucks - perphaps the newsgroup could introduce a
voluntary donation to a charity of choice for really good advice.
"Jerry Spivey" wrote:

> If you're anything like me you couldn't stop this even if you wanted to or
> was rich enough to. We'd get bored ;-)
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:eOrObWZ2FHA.2604@.TK2MSFTNGP12.phx.gbl...
>
>sql