Sequence creation

Question

Sequence creation

Viewing 15 posts - 16 through 29 (of 29 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

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... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

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

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)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

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

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)

NotoriousDBA SSChasing Mays Points: 658 More actions · Answer 4

Jeff Moden (9/8/2011)
Fine... I don't like your implication. 😉

Fair enough.:-P

LutzM SSC Guru Points: 107049 More actions · Answer 5

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.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 6

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.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

NotoriousDBA SSChasing Mays Points: 658 More actions · Answer 7

E99999

F00001

...

FT9999

FU0001

...

FUB999

FUC001

...

FUCJ99

What would be the next value in the sequence?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

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

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)

LutzM SSC Guru Points: 107049 More actions · Answer 9

notoriousdba (9/8/2011)
E99999
F00001
...
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.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

NotoriousDBA SSChasing Mays Points: 658 More actions · Answer 10

NotoriousDBA

SSChasing Mays

Points: 658

September 8, 2011 at 12:37 pm

#1378847

LutzM,

Ah, you're so right. I see it now. My mistake.

LutzM SSC Guru Points: 107049 More actions · Answer 11

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...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

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

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)

SQLServerMS SSC Veteran Points: 223 More actions · Answer 13

Can anybody provide the script for getting the sequence

Have a nice day!

LutzM SSC Guru Points: 107049 More actions · Answer 14

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?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 15

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.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]