hefterr
asked on
Coldfusion/SQL Randomize Algorithm
Hi Experts,
1) This may not exactly be a ColdFusion question, but I am going to program it in CF v10 and you guys are very smart! SQL Guys too!!!!
Problem: I need to generate in a random sequence a list if all possible 4 digit numbers only using the set of digits (1,2,3,4,5)
ex: 1234, 4455, 5555, 1111.
I'm not sure how to do this best. I could
- generate a random 4 digit number from the set of (1,2,3,4,5). I think I did that somewhere before in a CAPTCHA routine.
- Search a table to make sure I didn't pick it already. If I did retry otherwise store the number in the next slot of the table.
But at the end, it will be like looking for a needle in a haystack when I only have a few possibilities left?
2) FYI. I also use SQL Server 2012 and this could be part of the solution.
-- Insert rows (Sequential number column, random column)
- Select all rows order by random column
3) ColdFusion guys: How do I generate the numbers sequentially between 1111 - 5555 only using the digits 1,2,3,4,5?
Any advise?
Thanks in advance,
hefterr
1) This may not exactly be a ColdFusion question, but I am going to program it in CF v10 and you guys are very smart! SQL Guys too!!!!
Problem: I need to generate in a random sequence a list if all possible 4 digit numbers only using the set of digits (1,2,3,4,5)
ex: 1234, 4455, 5555, 1111.
I'm not sure how to do this best. I could
- generate a random 4 digit number from the set of (1,2,3,4,5). I think I did that somewhere before in a CAPTCHA routine.
- Search a table to make sure I didn't pick it already. If I did retry otherwise store the number in the next slot of the table.
But at the end, it will be like looking for a needle in a haystack when I only have a few possibilities left?
2) FYI. I also use SQL Server 2012 and this could be part of the solution.
-- Insert rows (Sequential number column, random column)
- Select all rows order by random column
3) ColdFusion guys: How do I generate the numbers sequentially between 1111 - 5555 only using the digits 1,2,3,4,5?
Any advise?
Thanks in advance,
hefterr
ASKER
Hi _agx_,
This is a "one shot deal". My boss asked me to give him a list of these random number (in a spreadsheet of 1 column). I didn't ask why - and didn't care much :). Si I'll run this once and that will be that.
So I was thinking of just loading a test table with all the possible sequential values
Q: Do I use NumberFormat and then use left() or mid() on each of the 4 characters to see if they only contain (1,2,3,4,5)? This was my thinking
On each insert I'd have a random column that SQL Server can generate (I forget the type).
Then I'd just
select mynum from testable order by randcolumn
for each result, I'd put it to a file or cut and past the result set - whatever.
Nothing fancy.
Thoughts,
hefterr
This is a "one shot deal". My boss asked me to give him a list of these random number (in a spreadsheet of 1 column). I didn't ask why - and didn't care much :). Si I'll run this once and that will be that.
So I was thinking of just loading a test table with all the possible sequential values
Q: Do I use NumberFormat and then use left() or mid() on each of the 4 characters to see if they only contain (1,2,3,4,5)? This was my thinking
On each insert I'd have a random column that SQL Server can generate (I forget the type).
Then I'd just
select mynum from testable order by randcolumn
for each result, I'd put it to a file or cut and past the result set - whatever.
Nothing fancy.
Thoughts,
hefterr
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi _agx_
Very nice solution. You had some syntax errors I could not resolve creating the temp table so I just created a real test table and inserted 5 rows.
You had syntax errors generating the numbers - but I did fix that:
SELECT t1.value +''+ t2.value +''+ t3.value + '' + t4.value AS theNumber
FROM test_12345 t1
CROSS JOIN test_12345 t2
CROSS JOIN test_12345 t3
CROSS JOIN test_12345 t4
ORDER BY newid()
I created a spreadsheet with 2 columns. All the 625 numbers sequentially and another column with a random result.
Thanks for you help!!!!
all-numbers.xls
Very nice solution. You had some syntax errors I could not resolve creating the temp table so I just created a real test table and inserted 5 rows.
You had syntax errors generating the numbers - but I did fix that:
SELECT t1.value +''+ t2.value +''+ t3.value + '' + t4.value AS theNumber
FROM test_12345 t1
CROSS JOIN test_12345 t2
CROSS JOIN test_12345 t3
CROSS JOIN test_12345 t4
ORDER BY newid()
I created a spreadsheet with 2 columns. All the 625 numbers sequentially and another column with a random result.
Thanks for you help!!!!
all-numbers.xls
ASKER
Thanks again. Just curious, was my CF code idea feasible looping from all numbers from 1111 - 5555 and using NUMBERFORMAT() and LEFT() to bypass numbers that contained the digits (6,7,8,9,0)?
Here is the final SQL based on your approach:
DECLARE @table TABLE ( value char(1) )
INSERT INTO @table ( value ) VALUES (1)
INSERT INTO @table ( value ) VALUES (2)
INSERT INTO @table ( value ) VALUES (3)
INSERT INTO @table ( value ) VALUES (4)
INSERT INTO @table ( value ) VALUES (5)
SELECT t1.value +''+ t2.value +''+ t3.value + '' + t4.value AS theNumber
FROM @table t1
CROSS JOIN @table t2
CROSS JOIN @table t3
CROSS JOIN @table t4
order by newid()
hefterr
Here is the final SQL based on your approach:
DECLARE @table TABLE ( value char(1) )
INSERT INTO @table ( value ) VALUES (1)
INSERT INTO @table ( value ) VALUES (2)
INSERT INTO @table ( value ) VALUES (3)
INSERT INTO @table ( value ) VALUES (4)
INSERT INTO @table ( value ) VALUES (5)
SELECT t1.value +''+ t2.value +''+ t3.value + '' + t4.value AS theNumber
FROM @table t1
CROSS JOIN @table t2
CROSS JOIN @table t3
CROSS JOIN @table t4
order by newid()
hefterr
> You had some syntax errors I could not resolve creating the temp table
Sorry, I had to retype the code from another screen and was trying to add the VALUES clause for 2012 on the fly. It's entirely possible I messed it up :) I tested under 2005, so I used the older syntax:
INSERT INTO SomeTable (...)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 ....
> , was my CF code idea feasible looping from all numbers from 1111 - 5555
Not sure .. honestly I didn't quite follow the idea. It's probably me .. my head's all stopped up with a cold :)
Sorry, I had to retype the code from another screen and was trying to add the VALUES clause for 2012 on the fly. It's entirely possible I messed it up :) I tested under 2005, so I used the older syntax:
INSERT INTO SomeTable (...)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 ....
> , was my CF code idea feasible looping from all numbers from 1111 - 5555
Not sure .. honestly I didn't quite follow the idea. It's probably me .. my head's all stopped up with a cold :)
ASKER
Nevermind - hope you feel better!!! There is a bad URI going around that lasts around 10 days. Good to be working from home!
Thanks! Yes, working from home definitely keeps you more healthy in that sense ;-)
I was curious about the syntax, so I ran it through sqlfiddle and the VALUES stuff worked for SQL 2012 (it wouldn't work if you're using 2005 though). Though I realized the quotes in the SELECT weren't necessary. (They were there from an earlier version when I was using INT instead of CHAR):
SELECT t1.value +''+ t2.value +''+ t3.value +''+ t4.value AS theNumber
So for posterity here's the final working version with table variables (at least w/SQL Server 2012 anyway :)
DECLARE @table TABLE ( value char(1) )
INSERT INTO @table ( value ) VALUES (1),(2),(3),(4),(5)
SELECT t1.value + t2.value + t3.value + t4.value AS theNumber
FROM @table t1
CROSS JOIN @table t2
CROSS JOIN @table t3
CROSS JOIN @table t4
ORDER BY NewID()
SELECT t1.value +''+ t2.value +''+ t3.value +''+ t4.value AS theNumber
So for posterity here's the final working version with table variables (at least w/SQL Server 2012 anyway :)
DECLARE @table TABLE ( value char(1) )
INSERT INTO @table ( value ) VALUES (1),(2),(3),(4),(5)
SELECT t1.value + t2.value + t3.value + t4.value AS theNumber
FROM @table t1
CROSS JOIN @table t2
CROSS JOIN @table t3
CROSS JOIN @table t4
ORDER BY NewID()
ASKER
OK Thanks. I was running locally with SQL 2005 so that's part of my problem.
I also cut/pasted your FIRST SQL example and it had a syntax error :
SELECT t1.value +''+ t2.value +''+ t3.value, t4.value AS theNumber
The others corrected it!
Thanks for closing the loop!!!!
I also cut/pasted your FIRST SQL example and it had a syntax error :
SELECT t1.value +''+ t2.value +''+ t3.value, t4.value AS theNumber
The others corrected it!
Thanks for closing the loop!!!!
(Edit) Yeah, that would do it. It was written for the version you mentioned in the OP (SQL Server 2012) but that syntax wouldn't work in 2005. That's what I get for trying to change versions on the fly ;-)
Gah... I wish EE would notify people of edits to posts! I saw that error shortly after posting and corrected it.
Gah... I wish EE would notify people of edits to posts! I saw that error shortly after posting and corrected it.
1. Create a table with all possible values (~600) [TableA]
2. Query the table for the "next value". (Sort by newID() to get random order)
3. Remove the "next value" from TableA to indicate it's used. Finally insert it into TableB
That said ... what happens when you run out of numbers in the range 1111-5555 ? ;-)
EDIT:
BTW, you could generate the initial values in SQL with a CROSS JOIN. Just be careful w/them. You could easily hose your db server by accidentally bringing back a billion rows if you're not paying attention to the CROSS JOIN. In this case it's only about ~600 rows
Open in new window