Menu
Duplicate:
Inserting random characters to MYSQL Database
How can I generate 100 records with 5 random characters and insert into the database with a query.
I want to insert into this table:
codes
id (auto-increment)
codes
540
In order to generate a 10 character string, we can use inbuilt functions 'rand()' and 'char()'.
SQL Server RAND() Function The RAND() function returns a random number between 0 (inclusive) and 1 (exclusive).
Random Integer RangeSELECT 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. SELECT FLOOR(RAND()*(25-10+1))+10; The formula above would generate a random integer number between 10 and 25, inclusive.
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.
Try this one -
SELECT CONCAT(
CHAR( FLOOR(65 + (RAND() * 25))),
CHAR( FLOOR(65 + (RAND() * 25))),
CHAR( FLOOR(65 + (RAND() * 25))),
CHAR( FLOOR(65 + (RAND() * 25))),
CHAR( FLOOR(65 + (RAND() * 25)))
) random_string;
This query generates ASCII codes from 'A' to 'Z' and generates a random string from them. I cannot say that this way is elegant, but it works;-)
103
INSERT INTO codes_tbl (codes) VALUES (SUBSTRING(MD5(RAND()) FROM 1 FOR 5));
That should take care of it.
44
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
