Filling empty characters

Hi,

I am importing barcodes however they are all different lengths and I need to bring them all up to 14 characters by adding zero's to the beginning of them (up to when the field is full).

The only way I can think is doing a count on the char length and then adding zero's in a loop but I think there must be an easier way? Any help appreciated.

Thanks
TimWhat about this?

declare @.line varchar(50),@.len int
set @.line='12345'
set @.len=10
select isnull(replicate('0',@.len-len(@.line)),'')+@.line|||..or the more low-tech:

set @.line = right(14, '00000000000000' + @.line)

blindman|||how low can you go?

Hey, I have to lookup just to see the top of the gutter...

FILLFACTOR 100 on Empty Table

Hi,
I have a nightly process that starts with empty tables to create tables that
will be read-only durring the day. At the end of the process I would like
to have the indexes compacted to FILLFACTOR = 100. My process creates the
tables in one code module and finializes their processing in another code
module. I would prefer to keep the CREATE INDEX statements with the CREATE
TABLE statements (I know that my tables will populate faster without the
nonclustered indexes, but I don't care if it takes 5 minutes instead of 3;
reliable maintenance is a more important factor).
I'm thinking that if a table is empty that it shouldn't matter whether I
create the indexes with a FILLFACTOR = 1 or = 100; I'll just have one page
per index. And, once I start adding data, the FILLFACTOR is not a factor in
index maintenance. (BOL says FILLFACTOR is only for index creation, I'm
assuming that index pages will always be split 50/50 as records are
inserted.)
However, once my processing is done, and if I have used FILLFACTOR = 100,
then I can use DBCC DBREINDEX 'TableName' and all indexes will be compressed
to 100% FILL. This way I don't have to add every index in my early code
module to the final code module, thereby making maintenance more reliable.
So, to wrap it up, am I correct in my assumption that FILLFACTOR is not a
factor for insert performance when starting with a completely empty table?
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:uwbh8yucGHA.536@.TK2MSFTNGP02.phx.gbl...
> So, to wrap it up, am I correct in my assumption that FILLFACTOR is not a
> factor for insert performance when starting with a completely empty table?
Yes; every observation in your post is correct.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org

FILLFACTOR 100 on Empty Table

Hi,
I have a nightly process that starts with empty tables to create tables that
will be read-only durring the day. At the end of the process I would like
to have the indexes compacted to FILLFACTOR = 100. My process creates the
tables in one code module and finializes their processing in another code
module. I would prefer to keep the CREATE INDEX statements with the CREATE
TABLE statements (I know that my tables will populate faster without the
nonclustered indexes, but I don't care if it takes 5 minutes instead of 3;
reliable maintenance is a more important factor).
I'm thinking that if a table is empty that it shouldn't matter whether I
create the indexes with a FILLFACTOR = 1 or = 100; I'll just have one page
per index. And, once I start adding data, the FILLFACTOR is not a factor in
index maintenance. (BOL says FILLFACTOR is only for index creation, I'm
assuming that index pages will always be split 50/50 as records are
inserted.)
However, once my processing is done, and if I have used FILLFACTOR = 100,
then I can use DBCC DBREINDEX 'TableName' and all indexes will be compressed
to 100% FILL. This way I don't have to add every index in my early code
module to the final code module, thereby making maintenance more reliable.
So, to wrap it up, am I correct in my assumption that FILLFACTOR is not a
factor for insert performance when starting with a completely empty table?
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:uwbh8yucGHA.536@.TK2MSFTNGP02.phx.gbl...
> So, to wrap it up, am I correct in my assumption that FILLFACTOR is not a
> factor for insert performance when starting with a completely empty table?
Yes; every observation in your post is correct.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org

Fill Column with Sequence

I apologize if this is redundant.

How would one fill an empty column with a sequence of numbers? The
column exists in a table with aproximately 1000000 rows of data. I
believe in oracle the following would work:

update foo set bar = rownum;

...but 'rownum' does not seem to exist in mssql. The numbers do not need
to be in order, but I would like to keep them somewhat small.

Any help would be appreciated.If you want to create a surrogate key (as seems to be implied by your
statement "The numbers do not need to be in order") you can add an IDENTITY
column. But perhaps you don't need to do that at all. If you explain your
actual requirement maybe we could advise you better.

--
David Portas
SQL Server MVP
--|||try this undocumented syntax if you like

declare @.seq int
set @.seq = 0 -- one less than the first number
update foo set @.seq = bar = @.seq + 1

"python1" <python1@.spamless.net> wrote in message
news:ccn1vj017rc@.enews3.newsguy.com...
I apologize if this is redundant.

How would one fill an empty column with a sequence of numbers? The
column exists in a table with aproximately 1000000 rows of data. I
believe in oracle the following would work:

update foo set bar = rownum;

...but 'rownum' does not seem to exist in mssql. The numbers do not need
to be in order, but I would like to keep them somewhat small.

Any help would be appreciated.|||Aaron W. West wrote:
> try this undocumented syntax if you like
> declare @.seq int
> set @.seq = 0 -- one less than the first number
> update foo set @.seq = bar = @.seq + 1

Works perfectly. Thank you.|||python1 wrote:

> Aaron W. West wrote:
>> try this undocumented syntax if you like
>>
>> declare @.seq int
>> set @.seq = 0 -- one less than the first number
>> update foo set @.seq = bar = @.seq + 1
>
> Works perfectly. Thank you.
I wonder whether MS SQL Server defines these row-wise semantics.
You may ask for trouble here similar to the olden days when folks
believed GROUP BY implies ORDER BY.
Doesn't MS SQL Server provide an identity() function? This seems so much
cleaner and more partable.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab|||>> How would one fill an empty column with a sequence of numbers? <<

Why would one fill a column with a sequence of numbers?

What does this magical PHYSICAL storage number mean in your LOGICAL data
model? Think about the basics for two seconds. How do you go to the
reality from which you derived your data model and verify it?

>> ...but 'rownum' does not seem to exist in mssql. <<

It does not exist in the Relational Model, actually.

>> The numbers do not need to be in order, but I would like to keep them
somewhat small. <<

Order? Rows in a table do not have any ordering by definition; this is
set oriented language. Now if you were in a 1950 sequential file system
instead of an RDBMS in the 21-st century, that would make sense.

What are you really trying to do? You have asked how to code a solution
to some actual problem, having already decided on the answer before
telling anyone the problem. Surely, your schema is not so screwed up
that you want to use this thing for a key!!

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Joe Celko wrote:
>>>How would one fill an empty column with a sequence of numbers? <<
>
> Why would one fill a column with a sequence of numbers?
> What does this magical PHYSICAL storage number mean in your LOGICAL data
> model? Think about the basics for two seconds. How do you go to the
> reality from which you derived your data model and verify it?
>
>>>...but 'rownum' does not seem to exist in mssql. <<
>
> It does not exist in the Relational Model, actually.
>
>>>The numbers do not need to be in order, but I would like to keep them
> somewhat small. <<
> Order? Rows in a table do not have any ordering by definition; this is
> set oriented language. Now if you were in a 1950 sequential file system
> instead of an RDBMS in the 21-st century, that would make sense.
> What are you really trying to do? You have asked how to code a solution
> to some actual problem, having already decided on the answer before
> telling anyone the problem. Surely, your schema is not so screwed up
> that you want to use this thing for a key!!
> --CELKO--
> ===========================
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Just to clear it up...

We are running a GIS database application called ArcSDE. The application
stores GIS data and uses MSSQL as a backend. Tables are created and data
imported through the application (ArcCatalog). In my case, the data
would not import into the table through the app, so it was loaded
through enterprise manager. There is one extra column in the SDE tables
named 'objectid' which runs in sequence and is used by the sde
application for keeping track of the records. After importing the data
though mangler the objectid column was 'null' because it had been set to
'ignore' in the import transform. Data would not display in the table
within ArcCatalog with this column empty. After running the query below:

declare @.seq int
set @.seq = 0
update foobar set @.seq = objectid = @.seq + 1

...the data could be displayed in the app.

The imports usually work correctly through the application, so this
measure will probably not be needed often.