??? said:
Any clever ays to stop me exporting to Excel & sorting?
Uninstall Excel said:
I have a data set that I want to randomise. I want to take
individual columns and re-arrange the data in them so that
the data I end up with does not look like the start.
It would be crazy to try to do this on the database side.
Take for example, this table:
CREATE TABLE Randoms
(
MyTextCol VARCHAR(10) NOT NULL
)
;
INSERT INTO Randoms
(MyTextCol) VALUES ('microsoft')
;
INSERT INTO Randoms (MyTextCol)
VALUES ('keithemery')
;
INSERT INTO Randoms (MyTextCol)
VALUES ('abcdefg')
;
You could use the following algorithm:
1. Select the text ('microsoft').
2. Start with the letter in position 1 ('m').
3. Randomly choose a subsequent letter ('f'); use the least
significant figures of the value returned by the NOW() function to
generate a random number.
4. Switch the two letters ('ficrosomt').
5. Repeat with the next letters in sequence; when you reach the end of
the text, use letter one again.
Passing through the whole word twice will yield a good level of
randomness.
However, in sql just one letter switch would look like this:
SELECT MID(MyTextCol,1,((0 MOD LEN(MyTextCol))+1)-1) &
MID(MyTextCol,(INT(((NOW()*100000)-INT(NOW()*100000))*(LEN(MyTextCol)-((0
MOD LEN(MyTextCol))+1)))+((0 MOD LEN(MyTextCol))+1)+1),1) &
MID(MyTextCol,((0 MOD
LEN(MyTextCol))+1)+1,(INT(((NOW()*100000)-INT(NOW()*100000))*(LEN(MyTextCol)-((0
MOD LEN(MyTextCol))+1)))+((0 MOD LEN(MyTextCol))+1)+1)-((0 MOD
LEN(MyTextCol))+1)-1) & MID(MyTextCol,((0 MOD LEN(MyTextCol))+1),1) &
MID(MyTextCol,(INT(((NOW()*100000)-INT(NOW()*100000))*(LEN(MyTextCol)-((0
MOD LEN(MyTextCol))+1)))+((0 MOD LEN(MyTextCol))+1)+1)
+1,LEN(MyTextCol)-(INT(((NOW()*100000)-INT(NOW()*100000))*(LEN(MyTextCol)-((0
MOD LEN(MyTextCol))+1)))+((0 MOD LEN(MyTextCol))+1)+1) ) AS MyTextCol
FROM Randoms
And a second pass would be this:
SELECT MID(DT1.MyTextCol,1,((1 MOD LEN(DT1.MyTextCol))+1)-1) &
MID(DT1.MyTextCol,(INT(((NOW()*100000)-INT(NOW()*100000))*(LEN(DT1.MyTextCol)-((1
MOD LEN(DT1.MyTextCol))+1)))+((1 MOD LEN(DT1.MyTextCol))+1)+1),1) &
MID(DT1.MyTextCol,((1 MOD
LEN(DT1.MyTextCol))+1)+1,(INT(((NOW()*100000)-INT(NOW()*100000))*(LEN(DT1.MyTextCol)-((1
MOD LEN(DT1.MyTextCol))+1)))+((1 MOD LEN(DT1.MyTextCol))+1)+1)-((1 MOD
LEN(DT1.MyTextCol))+1)-1) & MID(DT1.MyTextCol,((1 MOD
LEN(DT1.MyTextCol))+1),1) &
MID(DT1.MyTextCol,(INT(((NOW()*100000)-INT(NOW()*100000))*(LEN(DT1.MyTextCol)-((1
MOD LEN(DT1.MyTextCol))+1)))+((1 MOD LEN(DT1.MyTextCol))+1)+1)
+1,LEN(DT1.MyTextCol)-(INT(((NOW()*100000)-INT(NOW()*100000))*(LEN(DT1.MyTextCol)-((1
MOD LEN(DT1.MyTextCol))+1)))+((1 MOD LEN(DT1.MyTextCol))+1)+1) ) AS
MyTextCol FROM (SELECT MID(T1.MyTextCol,1,((0 MOD
LEN(T1.MyTextCol))+1)-1) &
MID(T1.MyTextCol,(INT(((NOW()*100000)-INT(NOW()*100000))*(LEN(T1.MyTextCol)-((0
MOD LEN(T1.MyTextCol))+1)))+((0 MOD LEN(T1.MyTextCol))+1)+1),1) &
MID(T1.MyTextCol,((0 MOD
LEN(T1.MyTextCol))+1)+1,(INT(((NOW()*100000)-INT(NOW()*100000))*(LEN(T1.MyTextCol)-((0
MOD LEN(T1.MyTextCol))+1)))+((0 MOD LEN(T1.MyTextCol))+1)+1)-((0 MOD
LEN(T1.MyTextCol))+1)-1) & MID(T1.MyTextCol,((0 MOD
LEN(T1.MyTextCol))+1),1) &
MID(T1.MyTextCol,(INT(((NOW()*100000)-INT(NOW()*100000))*(LEN(T1.MyTextCol)-((0
MOD LEN(T1.MyTextCol))+1)))+((0 MOD LEN(T1.MyTextCol))+1)+1)
+1,LEN(T1.MyTextCol)-(INT(((NOW()*100000)-INT(NOW()*100000))*(LEN(T1.MyTextCol)-((0
MOD LEN(T1.MyTextCol))+1)))+((0 MOD LEN(T1.MyTextCol))+1)+1) ) AS
MyTextCol FROM Randoms T1) DT1
which would of course be madness to even attempt to write in the first
place.
Conclusion: I think this is something to be attempted using a
procedural (rather than set based) approach. In other words, yes, use
Excel and VBA.
Jamie.
--
