Hard coding leading zeros to Unique IDs

Question

Hard coding leading zeros to Unique IDs

mashikoo

Ten Centuries

Points: 1188
More actions
October 27, 2010 at 6:51 am

#230949

I'm trying to include leading zeros to unique IDs in my query so that when it's executed, the zeros will be right before the unique ids in the database e.g 852694 becomes 000852694. Any suggestion will be appreciated.
Thanks.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

Guessing that your unique ID's are integer, you will need to cast them to a string type, preferably a CHAR of the correct length.

Why do you want to do this?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

mashikoo Ten Centuries Points: 1188 More actions · Answer 2

Thanks for your response. Actually this Unique Ids are not integers, they are just numbers already generated for every employee in my organization. So, they wanted leading zeros included for benefit purposes. So I thought I could just hard code the zeros but there are lots and different number for each employee. I'm at a road block. Please help!!!

steveb. SSC-Forever Points: 46733 More actions · Answer 3

as mentioned if they have leading zeros then they are no longer numbers(integers etc..) then they have to be treated a Character dataypes.

you will need to cast the numbers to a char datatype and then add the leading '000'

sturner SSC-Insane Points: 22411 More actions · Answer 4

The leading zeros are meaningless to SQL server. If your company wants to see them just modify the formatting on forms and reports so they appear that way. There is no need nor is there any advantage to trying to maintain numbers that way in a database column.

The probability of survival is inversely proportional to the angle of arrival.

mashikoo Ten Centuries Points: 1188 More actions · Answer 5

Thanks you guys are great. Your information are helpful

Lowell SSC Guru Points: 323507 More actions · Answer 6

you can add a calculated column to create the desired version and leave the original integer data untouched; you can do the same in the presentation layer as well.

ie

ALTER TABLE MyTable Add FORMATTEDID AS RIGHT('000000000000' + CONVERT(VARCHAR,UniqueID),12) PERSISTED

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mashikoo Ten Centuries Points: 1188 More actions · Answer 7

Hi Lowell,

That's exactly what I'm trying to do add a calculated column to create the desired version and leave the original integer data untouched but i don't to Alter any table. What do you think?

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 8

mashikoo (10/27/2010)
I'm trying to include leading zeros to unique IDs in my query

Can you post the query? Also the DDL for one of the tables?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

sturner SSC-Insane Points: 22411 More actions · Answer 9

If you can modify the form and report code to utilize this derived data column than you can just as easily provide a leading zero formatted version of the column.

Personally I would not want to add this sort of bloat to my database just for this. But its up to you....

The probability of survival is inversely proportional to the angle of arrival.

Lowell SSC Guru Points: 323507 More actions · Answer 10

mashikoo (10/27/2010)
Hi Lowell,
That's exactly what I'm trying to do add a calculated column to create the desired version and leave the original integer data untouched but i don't to Alter any table. What do you think?

can you make your applications select from a view, which formats the number, instead of the direct base table?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mashikoo Ten Centuries Points: 1188 More actions · Answer 11

I wouldn't want to do that either. I guess they'll just have to do the modification on the report. I appreciate your efforts. SSC rocks!!!!