InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 15:22:04 GMT20http://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspx[quote][b]Eugene Elutin (10/30/2012)[/b][hr][quote][b]Jeff Moden (10/30/2012)[/b][hr][quote][b]dwain.c (10/29/2012)[/b][hr][quote][b]Jeff Moden (10/29/2012)[/b][hr]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.[/quote]Sorry about that! Just couldn't help the shameless plug for my article. :-D[/quote]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.[/quote]I would wait, until senior managers received the report with:Product | Sold Quantity | ProfitCRAP | 10000 | $1,000,000SH*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:[/quote]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.Tue, 30 Oct 2012 19:38:46 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspx[quote][b]Jeff Moden (10/30/2012)[/b][hr][quote][b]dwain.c (10/29/2012)[/b][hr][quote][b]Jeff Moden (10/29/2012)[/b][hr]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.[/quote]Sorry about that! Just couldn't help the shameless plug for my article. :-D[/quote]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.[/quote]I would wait, until senior managers received the report with:Product | Sold Quantity | ProfitCRAP | 10000 | $1,000,000SH*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:Tue, 30 Oct 2012 07:21:56 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspx[quote][b]dwain.c (10/29/2012)[/b][hr][quote][b]Jeff Moden (10/29/2012)[/b][hr]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.[/quote]Sorry about that! Just couldn't help the shameless plug for my article. :-D[/quote]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.Tue, 30 Oct 2012 07:08:37 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspx[quote][b]Jeff Moden (10/29/2012)[/b][hr]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.[/quote]Sorry about that! Just couldn't help the shameless plug for my article. :-DMon, 29 Oct 2012 22:21:22 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspxIt's a fun problem but I wouldn't actually help anyone put such a thing into production for the reasons I've already stated.Mon, 29 Oct 2012 22:17:49 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspxNot a real fast solution but it is generating 1,679,616 permutations:[code="sql"]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 @tSELECT 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 TuplesFROM nTuplesWHERE n = 4ORDER BY Tuples[/code]From this article: [url]http://www.sqlservercentral.com/articles/sql+n-Tuples/89809/[/url]Mon, 29 Oct 2012 20:33:24 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspx[quote][b]capnhector (10/29/2012)[/b][hr][quote][b]ChrisM@Work (10/29/2012)[/b][hr][quote][b]Jeff Moden (10/29/2012)[/b][hr][quote][b]Eugene Elutin (10/29/2012)[/b][hr][quote][b]Jeff Moden (10/27/2012)[/b][hr]I wonder how many 3 and 4 letter swear words such a proccess will actually spell out...[/quote]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:)[/quote]That's why we shouldn't be Russian to use incrementing Alpha-Numerics. :-P[/quote]<<Groan>> Jeff that's awful![/quote]+1 for thats awfuland 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.[/quote]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.Mon, 29 Oct 2012 16:57:54 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspx[quote]there are plenty of products around which would perfectly qualify for such codes as CRAP, SH*T and other...[/quote]+100Mon, 29 Oct 2012 14:36:13 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspx[quote][b]Sean Lange (10/29/2012)[/b][hr][quote][b]Jeff Moden (10/27/2012)[/b][hr]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.[/quote]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.[/quote]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:Mon, 29 Oct 2012 14:06:39 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspx[quote][b]Jeff Moden (10/27/2012)[/b][hr]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.[/quote]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.Mon, 29 Oct 2012 12:44:45 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspx[quote][b]ChrisM@Work (10/29/2012)[/b][hr][quote][b]Jeff Moden (10/29/2012)[/b][hr][quote][b]Eugene Elutin (10/29/2012)[/b][hr][quote][b]Jeff Moden (10/27/2012)[/b][hr]I wonder how many 3 and 4 letter swear words such a proccess will actually spell out...[/quote]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:)[/quote]That's why we shouldn't be Russian to use incrementing Alpha-Numerics. :-P[/quote]<<Groan>> Jeff that's awful![/quote]+1 for thats awfuland 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.Mon, 29 Oct 2012 12:24:45 GMTCapnHectorhttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspx[quote][b]Jeff Moden (10/29/2012)[/b][hr][quote][b]Eugene Elutin (10/29/2012)[/b][hr][quote][b]Jeff Moden (10/27/2012)[/b][hr]I wonder how many 3 and 4 letter swear words such a proccess will actually spell out...[/quote]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:)[/quote]That's why we shouldn't be Russian to use incrementing Alpha-Numerics. :-P[/quote]<<Groan>> Jeff that's awful!Mon, 29 Oct 2012 06:37:28 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspx[quote][b]Eugene Elutin (10/29/2012)[/b][hr][quote][b]Jeff Moden (10/27/2012)[/b][hr]I wonder how many 3 and 4 letter swear words such a proccess will actually spell out...[/quote]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:)[/quote]That's why we shouldn't be Russian to use incrementing Alpha-Numerics. :-PMon, 29 Oct 2012 06:22:03 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspx[quote][b]Jeff Moden (10/27/2012)[/b][hr]I wonder how many 3 and 4 letter swear words such a proccess will actually spell out...[/quote]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:)Mon, 29 Oct 2012 05:04:59 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspxI 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.Sat, 27 Oct 2012 23:43:46 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspx[quote][b]Cadavre (10/8/2012)[/b][hr][quote][b]Skanda (10/8/2012)[/b][hr]Thank U Boss,It is looking great...but i need every record should have a numeric and alpabet,in present code, some records are only alphabetic....[/quote]In your original post, you said: -[quote][b]Skanda (10/8/2012)[/b][hr]0001, 0002, ... , 0009, 000A, ... , 000Z, ... , 0010, 0011, ... , 001A, ... and so on till... , ZZZZ[/quote]"ZZZZ" doesn't have numeric values.[/quote]And "0001, 0002, ... , 0009..." have no alpha characters...I guess the method I've offered in the following thread might help you:[url]http://www.sqlservercentral.com/Forums/Topic1267659-391-1.aspx[/url]Mon, 08 Oct 2012 07:17:20 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspx[quote][b]Skanda (10/8/2012)[/b][hr]Thank U Boss,It is looking great...but i need every record should have a numeric and alpabet,in present code, some records are only alphabetic....[/quote]In your original post, you said: -[quote][b]Skanda (10/8/2012)[/b][hr]0001, 0002, ... , 0009, 000A, ... , 000Z, ... , 0010, 0011, ... , 001A, ... and so on till... , ZZZZ[/quote]"ZZZZ" doesn't have numeric values.Mon, 08 Oct 2012 06:43:28 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspx[quote][b]Skanda (10/8/2012)[/b][hr]Hi,I need to write a SQL query to print the following aphanumberic sequence in SQL 2008.0001, 0002, ... , 0009, 000A, ... , 000Z, ... , 0010, 0011, ... , 001A, ... and so on till... , ZZZZall characters should be in UPPERCASE. Please Help me...Thanks in advance[/quote]Here's a TSQL equivalent of the Oracle CTE:[code="sql"]SELECT n, CHAR(n + CASE WHEN n < 10 THEN 48 ELSE 55 END)FROM (SELECT TOP(36) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM sys.columns a, sys.columns b) tORDER BY n[/code]Can you figure it out from here?Mon, 08 Oct 2012 06:20:13 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspxThank U Boss,It is looking great...but i need every record should have a numeric and alpabet,in present code, some records are only alphabetic....Mon, 08 Oct 2012 06:16:58 GMTMinnuhttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspxin query trying for 1000, but i need max.if it is more than 36*36*36*36, it helps me a lot...Mon, 08 Oct 2012 06:11:33 GMTMinnuhttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspx[quote][b]Skanda (10/8/2012)[/b][hr]Below code is working in 'Oracle'i need same output in sql.....[b]with digits as( select n, chr(mod(n,36)+case when mod(n,36) < 10 then 48 else 55 end) d from (Select rownum-1 as n from dual connect by level < 37))select d0.n*36*36*36*36 + d1.n*36*36*36 + d2.n*36*36 + d3.n*36 + d4.n, d0.d||d1.d||d2.d||d3.d||d4.dfrom digits d0,digits d1, digits d2, digits d3, digits d4[/b]Plz[/quote]Chris covered how in his post, did you try it?[code="sql"]SELECT a.Chr+b.Chr+c.Chr+d.ChrFROM (VALUES('0'),('1'),('2'),('3'),('4'),('5'),('6'), ('7'),('8'),('9'),('A'),('B'),('C'),('D'), ('E'),('F'),('G'),('H'),('I'),('J'),('K'), ('L'),('M'),('N'),('O'),('P'),('Q'),('R'), ('S'),('T'),('U'),('V'),('W'),('X'),('Y'), ('Z') )a(Chr)CROSS JOIN (VALUES('0'),('1'),('2'),('3'),('4'),('5'),('6'), ('7'),('8'),('9'),('A'),('B'),('C'),('D'), ('E'),('F'),('G'),('H'),('I'),('J'),('K'), ('L'),('M'),('N'),('O'),('P'),('Q'),('R'), ('S'),('T'),('U'),('V'),('W'),('X'),('Y'), ('Z') )b(Chr)CROSS JOIN (VALUES('0'),('1'),('2'),('3'),('4'),('5'),('6'), ('7'),('8'),('9'),('A'),('B'),('C'),('D'), ('E'),('F'),('G'),('H'),('I'),('J'),('K'), ('L'),('M'),('N'),('O'),('P'),('Q'),('R'), ('S'),('T'),('U'),('V'),('W'),('X'),('Y'), ('Z') )c(Chr)CROSS JOIN (VALUES('0'),('1'),('2'),('3'),('4'),('5'),('6'), ('7'),('8'),('9'),('A'),('B'),('C'),('D'), ('E'),('F'),('G'),('H'),('I'),('J'),('K'), ('L'),('M'),('N'),('O'),('P'),('Q'),('R'), ('S'),('T'),('U'),('V'),('W'),('X'),('Y'), ('Z') )d(Chr)ORDER BY a.Chr, b.Chr, c.Chr, d.Chr;[/code]Mon, 08 Oct 2012 06:09:58 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspx[quote][b]Skanda (10/8/2012)[/b][hr]Hi,not exactly like home work, as per my requirement only am posting.i've tried below code, but it is generating only number.WITH Sequence ( SeqNo) as( SELECT 1 UNION ALL SELECT SeqNo + 1 FROM Sequence WHERE SeqNo < 1000)SELECT TOP 1000 * FROM SequenceOPTION ( MAXRECURSION 0);GOplease help me......[/quote]You're looking at the top 1000 in your query, have you thought about how many rows there will actually be?You have 36 characters in total for each digit (0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z). So we're looking at 36*36*36*36 == 1,679,616 rows.Mon, 08 Oct 2012 06:05:34 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspxBelow code is working in 'Oracle'i need same output in sql.....[b]with digits as( select n, chr(mod(n,36)+case when mod(n,36) < 10 then 48 else 55 end) d from (Select rownum-1 as n from dual connect by level < 37))select d0.n*36*36*36*36 + d1.n*36*36*36 + d2.n*36*36 + d3.n*36 + d4.n, d0.d||d1.d||d2.d||d3.d||d4.dfrom digits d0,digits d1, digits d2, digits d3, digits d4[/b]PlzMon, 08 Oct 2012 06:02:42 GMTMinnuhttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspxUse table constructor to generate your primary sequence, within a CTE. Then run a select from it, joined to itself 3 times (giving 4 references total in the FROM list). They can be CROSS JOINS or old fashioned comma joins. It works just fine. Have a try, post back if you are unsure.Mon, 08 Oct 2012 05:59:51 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspxHi,not exactly like home work, as per my requirement only am posting.i've tried below code, but it is generating only number.WITH Sequence ( SeqNo) as( SELECT 1 UNION ALL SELECT SeqNo + 1 FROM Sequence WHERE SeqNo < 1000)SELECT TOP 1000 * FROM SequenceOPTION ( MAXRECURSION 0);GOplease help me......Mon, 08 Oct 2012 05:49:29 GMTMinnuhttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspxForgive me if I'm mistaken, but all of your posts look like homework. What have you tried so far?Mon, 08 Oct 2012 05:45:31 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1369729-392-1.aspxHi,I need to write a SQL query to print the following aphanumberic sequence in SQL 2008.0001, 0002, ... , 0009, 000A, ... , 000Z, ... , 0010, 0011, ... , 001A, ... and so on till... , ZZZZall characters should be in UPPERCASE. Please Help me...Thanks in advanceMon, 08 Oct 2012 05:38:00 GMTMinnu