Alphanumeric number generation

Question

Alphanumeric number generation

Viewing 12 posts - 16 through 26 (of 26 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Jeff Moden (10/29/2012)
Eugene Elutin (10/29/2012)
Jeff Moden (10/27/2012)
I wonder how many 3 and 4 letter swear words such a proccess will actually spell out...
Actually it depends how you take it, at least some of them will be easy to remember :-D.
By the way, English as a language is not very rich with such words in compare with some other languages (eg. world-champion in this business: Russian :hehe:)
That's why we shouldn't be Russian to use incrementing Alpha-Numerics. 😛

<<Groan>> Jeff that's awful!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

CapnHector SSCoach Points: 16143 More actions · Answer 2

ChrisM@Work (10/29/2012)
Jeff Moden (10/29/2012)
Eugene Elutin (10/29/2012)
Jeff Moden (10/27/2012)
I wonder how many 3 and 4 letter swear words such a proccess will actually spell out...
Actually it depends how you take it, at least some of them will be easy to remember :-D.
By the way, English as a language is not very rich with such words in compare with some other languages (eg. world-champion in this business: Russian :hehe:)
That's why we shouldn't be Russian to use incrementing Alpha-Numerics. 😛
<<Groan>> Jeff that's awful!

+1 for thats awful

and even if we increase it to 5 alphanumeric we just increase the number of dirty words. of course if these are customer records who would not love the code 04SS as their customer number.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Jeff Moden (10/27/2012)
I wonder how many 3 and 4 letter swear words such a proccess will actually spell out.
I strongly recommend not using incrementing alpha-numerics for just such a reason but if you do, you really need to avoid the use of vowels at the very least.

There are plenty of consonants that make swear words out of nothing (V, Y, K). I certainly don't want to be customer Foxtrot Victor Charlie Kilo. There are also others that seems harmless until you put them together VYLE. I probably am vile but don't want that to be my customer number. So much safer to stick arbitrary digits.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

Sean Lange (10/29/2012)
Jeff Moden (10/27/2012)
I wonder how many 3 and 4 letter swear words such a proccess will actually spell out.
I strongly recommend not using incrementing alpha-numerics for just such a reason but if you do, you really need to avoid the use of vowels at the very least.
There are plenty of consonants that make swear words out of nothing (V, Y, K). I certainly don't want to be customer Foxtrot Victor Charlie Kilo. There are also others that seems harmless until you put them together VYLE. I probably am vile but don't want that to be my customer number. So much safer to stick arbitrary digits.

I don't think that 4-characters were intended to be used as customer numbers. I would think it is more to do with Product Codes. And you like it or not, there are plenty of products around which would perfectly qualify for such codes as CRAP, SH*T and other... :hehe:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

there are plenty of products around which would perfectly qualify for such codes as CRAP, SH*T and other...

+100

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 6

capnhector (10/29/2012)
ChrisM@Work (10/29/2012)
Jeff Moden (10/29/2012)
Eugene Elutin (10/29/2012)
Jeff Moden (10/27/2012)
I wonder how many 3 and 4 letter swear words such a proccess will actually spell out...
Actually it depends how you take it, at least some of them will be easy to remember :-D.
By the way, English as a language is not very rich with such words in compare with some other languages (eg. world-champion in this business: Russian :hehe:)
That's why we shouldn't be Russian to use incrementing Alpha-Numerics. 😛
<<Groan>> Jeff that's awful!
+1 for thats awful
and even if we increase it to 5 alphanumeric we just increase the number of dirty words. of course if these are customer records who would not love the code 04SS as their customer number.

5 characters starts to allow "leet" to come into play such as id10t.

I'll say it again... incrementing alpha-numerics is a really, really bad idea. Even Delta screwed up on my confirmation number once. I happened to be flying east-bound from Detroit to Providence, RI to see my Mom and Dad. My confirmation number was "4EBSOB" (for east bound SOB). I could have had some real fun with that but let it go.

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

Dwain Camps SSC Guru Points: 86978 More actions · Answer 7

Not a real fast solution but it is generating 1,679,616 permutations:

DECLARE @t TABLE (strcol VARCHAR(4))

DECLARE @Alphanumerics CHAR(36) =

'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'

;WITH Tally (n) AS (

SELECT n=number

FROM [master].dbo.spt_values Tally

WHERE [Type] = 'P' AND Number BETWEEN 1 AND 36)

INSERT INTO @t

SELECT SUBSTRING(@Alphanumerics, n, 1)

FROM Tally

;WITH nTuples (n, Tuples) AS (

SELECT 1, CAST(strcol AS VARCHAR(4))

FROM @t

UNION ALL

SELECT 1 + n.n, CAST(t.strcol + n.Tuples AS VARCHAR(4))

FROM nTuples n

CROSS APPLY (

SELECT strcol

FROM @t t

) t

WHERE n < 4

)

SELECT Tuples

FROM nTuples

WHERE n = 4

ORDER BY Tuples

From this article: http://www.sqlservercentral.com/articles/sql+n-Tuples/89809/

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

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

It's a fun problem but I wouldn't actually help anyone put such a thing into production for the reasons I've already stated.

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

Dwain Camps SSC Guru Points: 86978 More actions · Answer 9

Jeff Moden (10/29/2012)
It's a fun problem but I wouldn't actually help anyone put such a thing into production for the reasons I've already stated.

Sorry about that! Just couldn't help the shameless plug for my article. 😀

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 10

dwain.c (10/29/2012)
Jeff Moden (10/29/2012)
It's a fun problem but I wouldn't actually help anyone put such a thing into production for the reasons I've already stated.
Sorry about that! Just couldn't help the shameless plug for my article. 😀

Nah... wasn't directed at you personally. This type of request comes up quite often and I just wanted people to know what a bad idea I think sequencial alphanumerics are by emphasizing it one more time.

I just hope that the people requesting this from the OP don't end up blaming the OP for the words that will be formed. Hopefully, the OP has warned them after all the warnings on this thread.

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 11

Jeff Moden (10/30/2012)
dwain.c (10/29/2012)
Jeff Moden (10/29/2012)
It's a fun problem but I wouldn't actually help anyone put such a thing into production for the reasons I've already stated.
Sorry about that! Just couldn't help the shameless plug for my article. 😀
Nah... wasn't directed at you personally. This type of request comes up quite often and I just wanted people to know what a bad idea I think sequencial alphanumerics are by emphasizing it one more time.
I just hope that the people requesting this from the OP don't end up blaming the OP for the words that will be formed. Hopefully, the OP has warned them after all the warnings on this thread.

I would wait, until senior managers received the report with:

Product | Sold Quantity | Profit

CRAP | 10000 | $1,000,000

SH*T | 50000 | $2,000,000

etc.

Actually, I think, if he will see good numbers in the last two columns, he will not give a sh*t about product coding practice :hehe:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Dwain Camps SSC Guru Points: 86978 More actions · Answer 12

Eugene Elutin (10/30/2012)
Jeff Moden (10/30/2012)
dwain.c (10/29/2012)
Jeff Moden (10/29/2012)
It's a fun problem but I wouldn't actually help anyone put such a thing into production for the reasons I've already stated.
Sorry about that! Just couldn't help the shameless plug for my article. 😀
Nah... wasn't directed at you personally. This type of request comes up quite often and I just wanted people to know what a bad idea I think sequencial alphanumerics are by emphasizing it one more time.
I just hope that the people requesting this from the OP don't end up blaming the OP for the words that will be formed. Hopefully, the OP has warned them after all the warnings on this thread.
I would wait, until senior managers received the report with:
Product | Sold Quantity | Profit
CRAP | 10000 | $1,000,000
SH*T | 50000 | $2,000,000
etc.
Actually, I think, if he will see good numbers in the last two columns, he will not give a sh*t about product coding practice :hehe:

If the manager can get that much profit out of CRAP and SH*T, who cares what they call it. That's what I need to be shovelling.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St