on 2010 Oct 20 11:42 AM
[This is a follow-up form the question "Find random free alphanumeric value" in the general newsgroup]:
If one wants to generate a sequence of 4 random digits/upper letters in SQL, how can this be done?
My intention is to use
However, as the range of the ASCII values for digits (48-57) and upper letters (65-90) is not adjacent, a simple usage of *CHAR(cast(truncnum(rand() * 36, 0) as int) + 48)* won't do.
Instead, based on the generated integer value, one would have to add 48 (for char indexes < 10) or 55. As a consequence, it's necessary to call CHAR() with an if expression as argument. In order to evaluate the expression, it must be used twice, i.e. for comparison and then to add the according constant.
Therefore, I have used a derived table to build the rand() values, as in the sample below:
select
rndInd1, rndInd1 + if rndInd1 < 10 then 48 else 55 end if as rndCharInd1, char(rndCharInd1) as chr1,
rndInd2, rndInd2 + if rndInd2 < 10 then 48 else 55 end if as rndCharInd2, char(rndCharInd2) as chr2,
rndInd3, rndInd3 + if rndInd3 < 10 then 48 else 55 end if as rndCharInd3, char(rndCharInd3) as chr3,
rndInd4, rndInd4 + if rndInd4 < 10 then 48 else 55 end if as rndCharInd4, char(rndCharInd4) as chr4
from (select
cast(truncnum(rand() * 36, 0) as int) as rndInd1,
cast(truncnum(rand() * 36, 0) as int) as rndInd2,
cast(truncnum(rand() * 36, 0) as int) as rndInd3,
cast(truncnum(rand() * 36, 0) as int) as rndInd4) S
But my attempts to use an IF EXPRESSION for that seem to fail. The syntax seems o.k. but I have the impression that the RAND values (as used in the aliased columns) are re-evaluated for each usage of the alias, and as such, are not deterministic during the evaluation of the expression. That's wrong, IMHO.
An exemplary output is:
34,53,'>',1,67,'5',20,72,'L',19,67,':'
Obviously, the 2nd column is not based on the value 34 that is used in the 1st column (it should be 34 + 55 = 89), and so on. As a consequence, the columns chr1 to chr4 with the desired character may return characters that are - in contrast to the requirement - neither digits nor upper letters, e.g. a colon.
I can bring this down to the simple query:
select rndInd1, rndInd1 + 0
from (select
cast(truncnum(rand() * 36, 0) as int) as rndInd1) S
IMHO, this should return a row with two identical values, with different results for each call.
But with SA 12.0.0.2566, it returns different columns, such as
16, 26 or 34, 17
Only when the second expression is simplified to a repetition of the same expression, then two identical values get returned:
select rndInd1, rndInd1
from (select
cast(truncnum(rand() * 36, 0) as int) as rndInd1) S
Resume:
Basically this seems a wrong treatment of rand()-based expressions in derived tables.
Maybe you want RAND() to be deterministic, but you will be lonely 🙂
The RAND function is treated as a non-deterministic function. The query optimizer does not cache the results of the RAND function.
http://dcx.sybase.com/index.html#1200en/dbreference/rand-function.html
Once upon a time (I think) RAND was deterministic, but that bug got fixed.
FWIW here's the code I use...
--------------------------------------------------------------------- BEGIN DROP FUNCTION generate_random_string; EXCEPTION WHEN OTHERS THEN END; CREATE FUNCTION generate_random_string ( IN @return_length INTEGER, IN @character_set VARCHAR ( 100 ) DEFAULT '' ) RETURNS LONG BINARY NOT DETERMINISTIC BEGIN DECLARE @characters_to_use LONG BINARY; DECLARE @character_count DOUBLE; DECLARE @random_number DOUBLE; DECLARE @return_position INTEGER; DECLARE @character_position INTEGER; DECLARE @return_value LONG BINARY; CASE @character_set WHEN 'caps' THEN SET @characters_to_use = STRING ( 'QWERTYUIOPASDFGHJKLZXCVBNM' ); WHEN 'letters' THEN SET @characters_to_use = STRING ( 'QWERTYUIOPASDFGHJKLZXCVBNM', 'qwertyuiopasdfghjklzxcvbnm' ); WHEN 'numbers' THEN SET @characters_to_use = STRING ( '1234567890' ); WHEN 'alphanumeric' THEN SET @characters_to_use = STRING ( 'QWERTYUIOPASDFGHJKLZXCVBNM', 'qwertyuiopasdfghjklzxcvbnm', '1234567890' ); WHEN 'loweralphanumeric' THEN SET @characters_to_use = STRING ( 'qwertyuiopasdfghjklzxcvbnm', '1234567890' ); WHEN 'upperalphanumeric' THEN SET @characters_to_use = STRING ( 'QWERTYUIOPASDFGHJKLZXCVBNM', '1234567890' ); ELSE SET @characters_to_use = STRING ( -- omitted: single quote, back slash 'QWERTYUIOPASDFGHJKLZXCVBNM', 'qwertyuiopasdfghjklzxcvbnm', '1234567890', '`~-=!@#$%^&*()_+{}|[]:"<>?;,./' ); END CASE; SET @character_count = LENGTH ( @characters_to_use ); SET @random_number = RAND(); SET @return_position = 1; WHILE @return_position <= @return_length LOOP SET @character_position = CAST ( ROUND ( @random_number * @character_count, 0 ) AS INTEGER ); SET @return_value = STRING ( @return_value, SUBSTR ( @characters_to_use, @character_position, 1 ) ); -- MESSAGE STRING ( @return_position, ', ', @random_number, ', ', @character_position, ', ', @return_value ) TO CLIENT; SET @random_number = RAND(); SET @return_position = @return_position + 1; END LOOP; RETURN @return_value; END; -- generate_random_string --------------------------------------------------------------------- -- Testing... /* BEGIN DECLARE @seed INTEGER; DECLARE @dummy DOUBLE; -- Note: DATEDIFF returns INTEGER so the range of @seed is limited. -- However, this logic will work well past the year 2055 since -- a negative seed is OK. -- Note: The extra "seeding" crap may no longer be necessary in V12. SET @seed = DATEDIFF ( SECOND, CURRENT TIMESTAMP, '2055 10 27 00:00:00' ); SET @dummy = RAND ( @seed ); SET @dummy = RAND(); SET @dummy = RAND(); SELECT CAST ( generate_random_string ( 5 ) AS VARCHAR ) AS "1", CAST ( generate_random_string ( 5, 'caps' ) AS VARCHAR ) AS "2", CAST ( generate_random_string ( 16, 'alphanumeric' ) AS VARCHAR ) AS "3", CAST ( generate_random_string ( 15, 'loweralphanumeric' ) AS VARCHAR ) AS "4", CAST ( generate_random_string ( 30, 'upperalphanumeric' ) AS VARCHAR ) AS "5"; END; 1,2,3,4,5 'yk2{j','XGBCL','SKJhKeOmxhAhGJWF','dxw4oef9rkocygh','FDQGCWZ6RIZQMJE447Y7BP84N0QOK8' */
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
No, that's a misunderstanding. I do want RAND() to be not deterministic, of course. Everything else would be nonsense. But I don't think that an expression based on RAND() should be re-evaluated each time is it referenced. And I remember that Glenn has pointed out in the general newsgroup a while ago that this kind of re-evaluation is not desired. (Haven't got the actual news thread, however). Sidenote: As stated in my comment, v8 treats that kind of non-determinism correctly: Different calls of RAND() within the same query are not deterministic, computations based on their results are.
You could create a mapping table with two columns "index","char" then you could select based on rand() from the mapping table, without the need to do any "if" or "calculation"
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Sure, and I could also write a stored proc that would select rand() into some kind of local temporary table and then use that for further processing. But the general (though not verbalized!) approach is to use just a query, i.e. not using any kind of DDL. Besides that, I'm mainly posting a bug report here, methinks:)
User | Count |
---|---|
66 | |
10 | |
10 | |
10 | |
10 | |
8 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.