September 8, 2011 at 11:18 am
Jeff Moden (9/7/2011)
SQLServerMS (9/7/2011)
Hi,I need to populate the values for a column in a sequential manner as below, these need to be auto generated:
i1st it starts with
000001
000002
......
999999
A00001
A00002
......
A99999
B00001
......
B99999
......
Z99999
ZA0001
......
ZA9999
......
ZZZZZZ
How can i achieve this.... please help me
Just imagine all of the nasty words that can be spelled with 3 to 6 letters. Nice way to get sued. Don't do this! 😉
I don't think there are any nasty words possible with this concept:
There will always be only one "character digit" other than [0..9] or [Z] due to the concept presented.
Of course, this will waste quite some possible combinations (e.g. 800AB5) but it will also eliminate the risk of "nasty words".
Or I misunderstood the concept... Quite possible, though... 😉
September 8, 2011 at 11:37 am
notoriousdba (9/8/2011)
Jeff Moden (9/8/2011)
notoriousdba (9/8/2011)
References, or it didn't happen.
I AM the reference! 😉 Do it your way and embarass your company and possibly get sued for offending someone's sensibilities. I can't wait to see what happens when you send someone a document with a serial number of A__HOLES or F__KHEAD on it. And I don't like your inference. Are you calling me a liar?
I, as the speaker, imply. You, as the listener, infer. And you infer incorrectly. I'm not calling you a liar, I just don't believe you without proof. It sounds apocryphal to me.
And BTW, it's not MY way, it's the OP's way. I expressed no opinion about what the OP wants to do, though I don't happen to like it very much. It strikes me as being fussy, and overly complicated, but I don't know what his requirements and constraints are.
Fine... I don't like your implication. 😉
--Jeff Moden
September 8, 2011 at 11:43 am
LutzM (9/8/2011)
Jeff Moden (9/7/2011)
SQLServerMS (9/7/2011)
Hi,I need to populate the values for a column in a sequential manner as below, these need to be auto generated:
i1st it starts with
000001
000002
......
999999
A00001
A00002
......
A99999
B00001
......
B99999
......
Z99999
ZA0001
......
ZA9999
......
ZZZZZZ
How can i achieve this.... please help me
Just imagine all of the nasty words that can be spelled with 3 to 6 letters. Nice way to get sued. Don't do this! 😉
I don't think there are any nasty words possible with this concept:
There will always be only one "character digit" other than [0..9] or [Z] due to the concept presented.
Of course, this will waste quite some possible combinations (e.g. 800AB5) but it will also eliminate the risk of "nasty words".
Or I misunderstood the concept... Quite possible, though... 😉
Consider which letters may occur between ZA9999 and ZZZZZZ. You can spell some pretty offensive words with just the 5 letters. 😉
Now, I realize that you need a fairly large number to go from 000001 to a bit after ZA9999 before the nasty words will kick in on this sequence but we also don't know how it will be used.
--Jeff Moden
September 8, 2011 at 11:43 am
Jeff Moden (9/8/2011)
Fine... I don't like your implication. 😉
Fair enough.:-P
September 8, 2011 at 11:55 am
Jeff Moden (9/8/2011)
...Consider which letters may occur between ZA9999 and ZZZZZZ. You can spell some pretty offensive words with just the 5 letters. 😉
Now, I realize that you need a fairly large number to go from 000001 to a bit after ZA9999 before the nasty words will kick in on this sequence but we also don't know how it will be used.
I don't think there are any offensive words possible:
Based on the description Z99999 is followed by ZA0001. I would expect ZA9999 would be followed by ZB0001 and continue to ZZ9999 without any character used in the last four digits.
Then it continues from ZZA001 and continue to ZZZ999 and so on.
I'd say this concept can only hold slightly less than 4.000.000 numbers compared to over 2 billion for a complete alphanumeric range.
September 8, 2011 at 12:04 pm
If my assumption of the concept is correct, it doesn't really make much sense to add the effort of a customized sequence just to cover 4 x the numeric range...
A char(6) data type even requires more storage than an integer data type. Assuming the full alphanumeric range for the former, both would still cover the same range of positive numbers.
September 8, 2011 at 12:17 pm
E99999
F00001
...
FT9999
FU0001
...
FUB999
FUC001
...
FUCJ99
What would be the next value in the sequence?
September 8, 2011 at 12:20 pm
LutzM (9/8/2011)
Jeff Moden (9/8/2011)
...Consider which letters may occur between ZA9999 and ZZZZZZ. You can spell some pretty offensive words with just the 5 letters. 😉
Now, I realize that you need a fairly large number to go from 000001 to a bit after ZA9999 before the nasty words will kick in on this sequence but we also don't know how it will be used.
I don't think there are any offensive words possible:
Based on the description Z99999 is followed by ZA0001. I would expect ZA9999 would be followed by ZB0001 and continue to ZZ9999 without any character used in the last four digits.
Then it continues from ZZA001 and continue to ZZZ999 and so on.
I'd say this concept can only hold slightly less than 4.000.000 numbers compared to over 2 billion for a complete alphanumeric range.
Ah... I see what you mean for this sequence. Thanks, Lutz.
--Jeff Moden
September 8, 2011 at 12:24 pm
notoriousdba (9/8/2011)
E99999F00001
...
FT9999
FU0001
...
FUB999
FUC001
...
FUCJ99
What would be the next value in the sequence?
Based on the description this would not be possible:
F00001
...
FT9999
Instead it would be
F00001
...
ZT9999
Otherwise the value before B00001would be AZZZZZ and not A99999 as stated.
But like I said earlier: it's guessing. Until the OP clarifies.
September 8, 2011 at 12:37 pm
LutzM,
Ah, you're so right. I see it now. My mistake.
September 8, 2011 at 12:48 pm
notoriousdba (9/8/2011)
LutzM,Ah, you're so right. I see it now. My mistake.
No mistake. Just a different interpretation.:-D
My whole chain of arguments is based on the sample data provided and might evaporate into dust once the OP clarifies differently. Or it might get rock solid. We might never know...
September 8, 2011 at 10:44 pm
notoriousdba (9/8/2011)
Jeff Moden (9/8/2011)
Fine... I don't like your implication. 😉
Fair enough.:-P
Heh... you had me doubting myself 😉 Inference was the word I was looking for because it appeared that you jumped to a conclusion. Your stating that inference was implication.
Anyway... welcome aboard. 🙂
--Jeff Moden
September 9, 2011 at 12:43 am
Can anybody provide the script for getting the sequence
Have a nice day!
September 9, 2011 at 1:02 am
SQLServerMS (9/9/2011)
that is what i required, can u tell me how i can achieve this in sql script
What exactly do you mean by "that"?
Is your goal to have about 4mill possible combinations or 2billion? (see a few post before)
If the former: I recommend use integer since it's just not worth the effort. If the latter: I still recommend to use integer. 😀
Would you please clarify what type of concept you're trying to use and why?
September 9, 2011 at 9:08 am
SQLServerMS (9/9/2011)
Can anybody provide the script for getting the sequence
What sequence are you looking for?
We've asked several times to clarify.
Deleting/rephrasing old posts doesn't really help...
Until we know what kind of sequence you're looking for, we can't really help you any further.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply