Generating a list of all combinations

Factual Questions
1

For some reason I’m drawing a blank on a simple way to do this. I ultimately need to generate a list of all possible 4 digit combinations that start with a fixed character. The digits are alphanumeric so the can be any thing from 0-9 and A-Z (upper case only, no special characters). The first character will be fixed (I’ll use X for this example) so what I ultimately need is a list of X000, X001, all the way up to XZZZ (46,656 possibilities I think).

I know there has to be a relatively simple way to do this but I’m not seeing it. I have Excel, Access, and SAS as tools I can use. Ultimately I’ll need to load the list into Access.

So, great wise Dopers… what is the simple solution that is escaping me?

2

Misleading title? I think you’re looking for an ordered list and not any permutations and combinations. At first I was thinking of using random.org. I’ve used that a few times but I think it only processes numerical data. You may want to look at that.

Won’t Excel’s mouse drag do this for you? Not sure of the official name. Start with two adjacent cells seeded with X000 and X001. Select both cells, click on the small + in the lower right of the last cell, and then drag to extend the range until you get to X009. Then manually enter X00A and X00B into the next cells and extend those…?

Will that work?

3

Hmmm… never tried to drag with anything other than numbers. Yes I do need an ordered list, but of all permutations and combinations… in other words, values like X9J4 are fine.

Just tried the click and drag, and did X001-X009 then typed in X00A and when I drag it it just copies X00A down. Tried to trick it by typing X00A then X00B and clicking both and dragging down but it just repeats X00A, X00B, X00A, X00B.

4

Do you have Basic?
Do this with three nested For loops, iterating though the ASCII values of 0-Z (hex 30-5A). You need to put a test in that says if value > 57(dec) then value = value+7 to account for some puncutation between ‘9’ and ‘A’. Do a CHR$(Value) to get the ASCII character.

5

The following Python script will do what you require:


import itertools
import string

foo = itertools.product(string.digits + string.ascii_uppercase,repeat = 3)

for c in foo:
    print 'X'+''.join(c)

You’ll probably want to dump the output to a file to make it usable by anything else.

6

Well, I found a simple solution after all… by doing what you are never supposed to do.

I used Access and created a table called Chars with values of 0-9 and A-Z. I then made two copies, Chars2 and Chars3 (same values in the tables). Then I created a query where I pulled in the Character field from each of the three tables but (here is the key) didn’t tie any of the tables to each other. Voila, I’ve now got a table with 46656 combinations. I’ll now just take that and concatenate an X and the three fields and I’ll have my list.

7

Glad you found a solution.

I’ve dragged with dates and it works. I needed column headers for every Monday date, so I seeded cells with 3/1, 3/8, 3/15, and then dragged and got the right dates in Apr, May, etc. It was great.

Sometimes when dragging you have to seed more han two cells before Excel recognizes the pattern, otherwise you get A - B - A - B or A - B - C - A - B - C instead of A - B - C - D. Not sure if there’s a rule on that. I figure there must be.

8

That is what I was about to suggest. Neglecting to join tables together in a relational database is normally bad news but this is the one use for it that I know of. The result is called a Cartesian product if you are curious.

9

What you’ve done here is nothing more nor less that a Cartesian Product. By “didn’t tie any of the tables to each other” I gather you mean your query had no WHERE or JOIN clauses.

BTW, you don’t need to make extra copies of the original table to do this. You can perfectly well join a table to itself. You should be able to write:

SELECT c1.char, c2.char, c3.char FROM Chars c1, Chars c2, Chars c3

You might even be able to get the concatenated 4-character string all in one step with something like:

SELECT “X” + c1.char + c2.char + c3.char FROM Chars c1, Chars c2, Chars c3

I’m not entirely sure that works, but I kinda think it ought to, at least in any standard form of SQL. Access might be a little bit idiosyncratic about it. Give it a try!

ETA: BTW, there is nothing illegitimate about joining a table to itself this way. It’s definitely not “doing what you are never supposed to do.” There are occasions where this is what you perfectly well might want to do.

10

(Missed edit window.)

More ETA: There’s no guarantee that you’ll get the results in the correct order (although you might). You can fix that with something like:

SELECT “X” + c1.char + c2.char + c3.char result
FROM Chars c1, Chars c2, Chars c3
ORDER BY result

11

Yup… tying, joining, linking… I’ve used so many different database tools and many tend to use different terms for the same thing.

Yes, that is the code basically created by the query. If you have ever “written” a query in Access, the basic way is very “drop and drag”. You go into query design, select the tables by clicking on a list, then dragging the fields you want to join from one table to the corresponding field in another table (you can then right click on the “line” joining the tables to create outer joins etc.), then selecting fields from the tables you want in your output and then adding any criteria, sorting, etc.

Anyway, I got my list with very little effort, but now have a different dilemma which I’ll be asking for help in IMHO shortly.

Thanks to everyone for your help and also teaching me that there is an official name for what I did.

12

I just had a chance to try out my idea:
SELECT “X” + c1.char + c2.char + c3.char result
FROM Chars c1, Chars c2, Chars c3
ORDER BY result;

It almost works. The ORDER BY result clause didn’t work (for reasons I don’t understand), but when I deleted that clause, it worked just fine. In particular:
[ul]
[li] This gets you the concatenated strings like X3W8 in one step[/li][li] You don’t need to duplicate the characters table for each of the characters.[/li][/ul]
You can view, enter, or edit any SQL query directly using “SQL view”. This is useful for learning SQL – Create your query using the design view and then look at the SQL view to see what it created. (I figured out how to do pivot tables that way.) SQL view is much more flexible – you can create complicated queries using a variety of SQL options, beyond the basics you can do with the drag-and-drop design view. It’s worth getting to know, if you don’t already.

Us old-school SQL folks don’t need no steenkin’ design views! :smiley:

13

Take it easy there soldier. I can make any database from Oracle to SQL Server twirl on the head of a pin if I want to just by typing something out in a text editor but the immediate solution I thought of when I read the OP was exactly the one he came up with. Access query designer is pretty cool and useful for these types of things. There is no need to make problem any more difficult than it needs to be. Efficient problem solving isn’t just about getting the right answer. It is also about getting it the quickest and easiest way as well and he did that.

A+ work.

14

Order by won’t recognize the column name “result” that you used in the select clause – only names that exist in the from clause so
ORDER BY “X” + c1.char + c2.char + c3.char is better. I think you can use ORDER BY 1 to order by the first column of the select which is the same thing in this case.

15

Is this a quirk of Access SQL? I’m pretty sure ORDER BY result would work in standard SQL. I think.

BTW: I got one detail wrong: In Access SQL, you MUST include the keyword AS in the SELECT clause (but the FROM clause worked just fine without it):
SELECT “X” + c1.char + c2.char + c3.char AS result

This works and produces a column named result. Omitting AS gives a syntax error. In standard SQL, it’s optional.

It’s the ORDER BY result clause I don’t understand. I was under the impression that this is perfectly cromulent, at least in standard SQL. But in Access, including this clause caused it to prompt me to enter a value for result :confused:

Buy, hey! I’m working here on a Linux mochine that happens to have a MySQL server running on it. If I can just find my notes with my MySQL user name and password, I should give it a try there too!

ETA: Shagnasty, us old-farts of the CLI generation do quick simple things with CLI too. It’s always seemed more of a hassle to me, to poke around with those drag-and-drop QBE-style query generators. Just typing out the bare naked SQL always seemed quicker and simpler to me.

16

Okay, I just tried it with MySQL.

With MySQL, the syntax is a bit different. You cannot concatenate strings with + or & or || but instead you must use the concat(x, y, z, …) function. There can be as many arguments as you want, all character strings, and it concatenates them all.

I made a table called chars with a single field called ch, a character string of fixed length 1, and inserted 5 records (with the characters 0, 1, 2, A, and B).

Here is my query:
SELECT concat (‘X’, c1.ch, c2.ch, c3.ch) result
FROM chars c1, chars c2, chars c3
ORDER BY result;

This worked perfectly well. The ORDER BY clause worked as shown. (Without this clause, the result set was not in the order that OP probably wants.) The keyword AS is optional. I think it’s a quirk of Access that the ORDER BY clause doesn’t work this way.

As for GUI interfaces: Us old farts would rather type than click!

17

Bully for MySQL then but ORDER BY result doesn’t work in SQL server and I’m pretty sure not in oracle.

And yes, I’d rather compose SQL in vi than drag and drop tables.