To get random number in mysql

Murali · #1 (**permalink**) 10-27-2007, 02:56 AM

How to get 4-digit Random number

The following code will generate 4 digit random number.
using this code you can get upto 300 digit random number.

Code:

SET @number:=4;
select cast(FLOOR(concat('1',REPEAT('0',@number-1)) + (RAND() * CONCAT('9',REPEAT('0',@number-1))))as binary);

output

4523
3821
9656

priyan · #2 (**permalink**) 10-29-2007, 03:40 AM

Hi Techies,

We can do it in this way also..

-- For getting four digit random number
SELECT FLOOR(1000+ RAND()*8999) FROM DUAL;

Murali · #3 (**permalink**) 11-04-2007, 09:09 PM

The following code will produce lower case random string length 4

Code:

SET @number:=4;
set @string:='';
SET @sql:=REPLACE('SET @t:=BENCHMARK(X,@string:=concat(@string,char(FLOOR(97+ RAND()*26))));','X',@number);
prepare stm1 from @sql;
execute stm1 ;
select @string;

OUTPUT

Code:

tifg
locm
pord

Murali · #4 (**permalink**) 11-04-2007, 09:12 PM

The following code will produce UPPER case random string length 4

Code:

SET @number:=4;
set @string:='';
SET @sql:=REPLACE('SET @t:=BENCHMARK(X,@string:=concat(@string,char(FLOOR(65+ RAND()*26))));','X',@number);
prepare stm1 from @sql;
execute stm1 ;
select @string;

OUTPUT

Code:

KTOS
WRET
ETEW

write2ashokkumar · #5 (**permalink**) 11-05-2007, 05:01 AM

hi,

how to get the unique random number and random string?
For example, i start the process to insert the data into the table,with the random value, at that time i need the unique value also.... how can we get this?

Regards,
S.Ashokkumar

Murali · #6 (**permalink**) 11-05-2007, 07:38 PM

Quote:

Originally Posted by write2ashokkumar

hi,

how to get the unique random number and random string?
For example, i start the process to insert the data into the table,with the random value, at that time i need the unique value also.... how can we get this?

Regards,
S.Ashokkumar

we have a function in Mysql UUID() [Universal Unique Identifier].

A UUID is a 128-bit number represented by a string of five hexadecimal numbers in aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee format:

* The first three numbers are generated from a timestamp.
* The fourth number preserves temporal uniqueness in case the timestamp value loses monotonicity (for example, due to daylight saving time).
* The fifth number is an IEEE 802 node number that provides spatial uniqueness

Code:

mysql> SELECT UUID();
        -> '8dcc1548-dd6d-102a-bf08-0019d123ad2a'

Murali · #7 (**permalink**) 11-05-2007, 07:44 PM

To get combination of UPPER , lower and number random string length 4

Code:

SET @number:=4;
set @string:='';
SET @sql:=REPLACE('SET @t:=BENCHMARK(X,@string:=concat(@string,CASE char(FLOOR(49+ RAND()*3)) WHEN 1 THEN char(FLOOR(97+ RAND()*26)) WHEN 2 THEN char(FLOOR(65+ RAND()*26)) WHEN 3 THEN char(FLOOR(48+ RAND()*9)) END));','X',@number);
prepare stm1 from @sql;
execute stm1 ;
select @string;

OUTPUT

Code:

Ur4z
E0kb
lpQn

Murali · #8 (**permalink**) 11-05-2007, 10:34 PM

To get 4 digit random number

Code:

SET @number:=4;
set @string:='';
SET @sql:=REPLACE('SET @t:=BENCHMARK(X,@string:=concat(@string,char(FLOOR(48+RAND()*9))));','X',@number);
prepare stm1 from @sql;
execute stm1 ;
select @string;

OUTPUT

Code:

1484
4532
5234

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
how to create random number in c?	Murali	C and C++ Programming	6	07-22-2008 11:26 PM
How do I generate a random number from php?	itbarota	PHP Programming	2	09-25-2007 07:57 AM
How do I generate a random number from php?	kingmaker	PHP Programming	2	07-24-2007 01:56 AM
MySql Random string	priyan	Database Support	3	07-13-2007 07:31 AM
Code:PHP Random Functions	pranky	PHP Programming	0	02-24-2007 12:01 AM