Menu
i want my function to generate float numbers ( like : -123.000 , 874.000 ) in range ( like between: 272 and 3357 ) and update every record's "pos_x" field with unique float numbers. I write this Code but i see my table's field are all identical and also integer and they are positive.
this is my code :
UPDATE Driver_tbl
SET pos_x = (ROUND((RAND()* 10000),0))
671
The usual way is to use the modulo of the checksum. Note that checksum(newid()) can produce -2,147,483,648 and cause integer overflow on abs() , so we need to use modulo on the checksum return value before converting it to absolute value. This generates a random number between 0 and 9999.
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.
SQL Server has a built-in function that generates a random number, the RAND() mathematical function. The RAND math function returns a random float value from 0 through 1. It can take an optional seed parameter, which is an integer expression (tinyint, smallint or int) that gives the seed or start value.
RAND() function : This function in SQL Server is used to return a random decimal value and this value lies in the range greater than and equal to zero (>=0) and less than 1. If we want to obtain a random integer R in the range i <= R < j, we have to use the expression “FLOOR(i + RAND() * (j − i))”.
RAND is evaluated once per query
You can seed it like this though using CHECKSUM(NEWID()) so it is random per row
UPDATE Driver_tbl
SET pos_x = ROUND(RAND(CHECKSUM(NEWID())) * (3357-272),0) + 272
However, you can also cut out the middle man if you are using ROUND(.., 0)
UPDATE Driver_tbl
SET pos_x = ABS(CHECKSUM(NEWID())) % 9999
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
