'Add' to a character.

Question

Post reply

'Add' to a character.

Viewing 3 posts - 31 through 32 (of 32 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

you cannot reuse the numbers if there are gaps...at least not easily.

assuming you want a 10 digit number, ie 1 with 9 preceeeding zeros:

'0000000001'

Alter Table MyTable Add MyCalculatedColumn As RIGHT('0000000000' + convert(varchar(30), MyOriginalColumn),10) PERSISTED

the PERSISTED command maes the value be stored in the table...so you could create an index on it to make it easily searchable

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!

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 2

Lowell (8/14/2009)
you cannot reuse the numbers if there are gaps...at least not easily.

I'd like to add to that.... there is no reason to "fill in the gaps". If a given number is missing, so what? That only time that it may matter is if you are using ID cards that have preprinted numbers on them. Is that the case here?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

saivko SSC Eights! Points: 898 More actions · Answer 3

Thanks again guys!

Too bad SQL 2000 doesn't have PERSISTED. I created a column, but it only changed pre-existing ones, and not new ones.

As for filling in the gaps... I'm more concerned about the remote possibility of running out of 9 digit numbers. There is a lot of unused space... but the current largest number is somewhat close to the limit relatively speaking. But again the chance of that happening is still pretty low... unless someone did some mass inserts, then deletions a few times to run up the index.

Though not 100% fool proof, I tried out an insert trigger based upon your two column principle. Seems to work great with single inserts.