Menu
If you wanted to generate a pseudorandom alphanumeric string using T-SQL, how would you do it? How would you exclude characters like dollar signs, dashes, and slashes from it?
909
There's no built-in way to generate a random string in MySQL, so you need to create a workaround using the provided functions. One of the easiest ways to generate a random string is to use a combination of the SUBSTR() function, the MD5() function, and the RAND() function.
If you need a string of random digits up to 32 characters for test data or just need some junk text to fill a field, SQL Server's NEWID() function makes this simple. NEWID() is used to create a new GUID (globally unique identifier), and we can use that as a base to get a string of random characters.
Generally speaking, with pure T-SQL, we can use Rand(), NewID(), CRYPT_GEN_RANDOM() and Convert/Cast/Substring T-SQL to create random strings.
To create a random integer number between two values (range), you can use the following formula: SELECT FLOOR(RAND()*(b-a+1))+a; Where a is the smallest number and b is the largest number that you want to generate a random number for.
Using a guid
SELECT @randomString = CONVERT(varchar(255), NEWID()) very short ...
51
Similar to the first example, but with more flexibility:
-- min_length = 8, max_length = 12 SET @Length = RAND() * 5 + 8 -- SET @Length = RAND() * (max_length - min_length + 1) + min_length -- define allowable character explicitly - easy to read this way an easy to -- omit easily confused chars like l (ell) and 1 (one) or 0 (zero) and O (oh) SET @CharPool = 'abcdefghijkmnopqrstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ23456789.,-_!$@#%^&*' SET @PoolLength = Len(@CharPool) SET @LoopCount = 0 SET @RandomString = '' WHILE (@LoopCount < @Length) BEGIN SELECT @RandomString = @RandomString + SUBSTRING(@Charpool, CONVERT(int, RAND() * @PoolLength) + 1, 1) SELECT @LoopCount = @LoopCount + 1 END I forgot to mention one of the other features that makes this more flexible. By repeating blocks of characters in @CharPool, you can increase the weighting on certain characters so that they are more likely to be chosen.
35
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
