Skip to main content
W
webhead_1
Known Participant
August 21, 2008
Question

padding unique identifier SQL-2000

  • August 21, 2008
  • 2 replies
  • 1550 views
Hello all.

I have a unique problem. I need to convert a field within a database table to an unquiet identifier field. The problem is that I need to keep the lead zero in the field and I also need that field to increment with leading zeros. Is there away I can solve this issue in MS-SQL 2000.

The number before I convert.
000000008

After the conversion
8

Any help will be greatly appreciated..
This topic has been closed for replies.

2 replies

D
Dan_Bracuk
Inspiring
August 21, 2008
Put an autoincrement field in your table.

Put a character field into your table

Write a trigger that updates the character field with a padded version of the first one.

Tell the vendor to use the character field.
P
paross1
Participating Frequently
August 21, 2008
quote:
Write a trigger that updates the character field with a padded version of the first one.
Wouldn't that lead to a case of a a trigger attempting to update a mutating table, or is that just an Oracle issue?

Phil
D
Dan_Bracuk
Inspiring
August 21, 2008
quote:
Originally posted by: paross1
quote:
Write a trigger that updates the character field with a padded version of the first one.
Wouldn't that lead to a case of a a trigger attempting to update a mutating table, or is that just an Oracle issue?

Phil

The OP said that the db was ms sql. The trigger I had in mind was to update the record with the padded value. The trigger would be run on insert.

I haven't done anything with triggers since computer school so maybe I'm missing something.
D
Dan_Bracuk
Inspiring
August 21, 2008
Add one to the number after you convert it and then pad it with an many leading zero's as you need.

W
webhead_1Author
Known Participant
August 21, 2008
I only wish it was that easy, and that is the way it is currently being done. But I need the database to automatically generate these ID'. I have vendor who are comming in from the out side that don't want to use that schema.
-
-__cfSearching__-
Inspiring
August 21, 2008
webhead_1 wrote:
> Is there away I can solve this issue in MS-SQL 2000.
> But I need the database to automatically generate these ID

You could still use an identity column to generate ids (ie. "8"). But use a view to format the values as "000000008". Then do your selects from the view instead of the table. A view is almost like a regular table in many respects.

--- TABLE
create table Vendor (
recordID int identity,
vendorName varchar(100)
)

--- VIEW
create view vVendorFormatted
as
select RecordID, -- the identity column
right( replicate('0', 9) + convert(varchar, recordID), 9) AS PaddedRecordID,
VendorName
from Vendor
go

--- Do selects from the view instead of the table
SELECT *
FROM vVendorFormatted

-- RESULTS

000000001 1 vendor a
000000002 2 vendor b
000000003 3 vendor c
000000004 4 vendor d
000000005 5 vendor e

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices