Need help adding random float in SQLite

Question

I am trying to add a random float to an existing value for an entire column in SQLite

Adding a number to an existing shouldn't be hard and UPDATE myTable SET Column1=Column1+1 should work. If I am correct it will get each value in column1 and add 1 to it.

The problem is getting the random number, my aim is to have something similar to this

UPDATE mytable SET Column1=(Column1 + (RAND Between 0.5 AND 0.8));

so each row has a different random float added that's been generated between 2 floats added to it.

My issue is that the only reference I have found for random under SQLite is that the random function generates an integer.

How did you try to convert the integer into a float? Why didn't it work? — CL., May 21 '14 at 13:48
I have not tried to convert the int into a float, first off I was not sure how to do that within a query and second of all, that still doesn't give me a float between two other floats, nearest thing to that would be to generate an int and then divide it to make a float, but that still relies on converting which I didn't (don't) know about. — SJones, May 21 '14 at 13:58
sorry if that sounded a little bit of a harsh comment, wasn't my intention but reading it it may feel that way, further more, something that I also read that the random function is only generated once per query, not sure if this is true, I am working on formulating a query with the conversion to a float. any heads up on this would be useful — SJones, May 21 '14 at 14:08
Casting int to float could result in a "exponential random", not uniform in 0..1 — arivero, Jul 27 '16 at 17:17

score 7 · Answer 1 · answered Feb 12 '16 at 18:09

You can get a random floating point number in the interval (-1, 1] from SQLite like so:

SELECT RANDOM() / CAST(-9223372036854775808 AS REAL)

Therefore, you can get a value in the standard random number interval of [0, 1) with:

SELECT 0.5 - RANDOM() / CAST(-9223372036854775808 AS REAL) / 2

...and modify as appropriate from there.

score 2 · Accepted Answer · answered May 21 '14 at 14:05

2

The documentation says:

The random() function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.

So to get a number between -1 and 1, you would have to divide the return value by 9223372036854775808.

To get a number between 0 and 1 instead, divide by 2×9223372036854775808, then add ½.

To get other ranges, scale and add an offset appropriately:

UPDATE MyTable
SET Column1 = Column1 + (random() / 18446744073709551616 + 0.5) * 0.3 + 0.5;

answered May 21 '14 at 14:05

CL.

173,858
17
217
259

Thanks for the answer, using your response to question I done more searching and got this UPDATE TableName SET Column1 = (Column1 + ((abs(random()) % (100-1) + 1)*0.001)) this creates a random float between 0.001 and 1 (at least it should as it has worked in my test query) I will test yours out too, out of interest which one would be more efficient? – SJones May 21 '14 at 14:30
sorry again, meant to be 0.1 and 0.001, to get the 0.01 to 1.0 edit the above with *0.01 `UPDATE TableName SET Column1 = (Column1 + ((abs(random()) % (100-1) + 1)*0.01))` – SJones May 21 '14 at 14:39
Thanks again for the information, much appreciated, any chance you could give a reference to the % part as I only found this example elsewhere on the internet (the sqlite page on the random function didn't show anything about it http://www.sqlite.org/c3ref/randomness.html ) so I am not sure about the `% (:High-:Low)+:Low` part sorry if this is a noob question I'm not too good at programming and fairly new to databases overall – SJones May 21 '14 at 14:50
see [Uniformity of random numbers taken modulo N](http://stackoverflow.com/questions/13104478/uniformity-of-random-numbers-taken-modulo-n) – CL. May 21 '14 at 15:07
This will not work, as 18446744073709551616 is equivalent to 0. – Mumbleskates Feb 12 '16 at 17:51
@Widdershins It's certainly not equal to 0. [Works for me](http://sqlfiddle.com/#!5/9eecb7/1483). – CL. Feb 12 '16 at 18:01
@CL. You're right, looks like a bug in SQLiteStudio. – Mumbleskates Feb 13 '16 at 10:41

score 2 · Answer 3 · answered Jan 04 '19 at 01:52

While the other answers here are equally valid ways of doing this, I should mention that SQLite has an API for calling foreign functions, which is exposed in the bindings for a number of languages. This could let you define a binding to your host language's random function instead of using the one that's built-in. In Python, for example, using their sqlite3 module, it's a simple as this :

import random, sqlite3
db_conn = sqlite3.connect(":memory:")

# A function named "random_between" with 2 arguments
# that calls the python function random.uniform
db_conn.create_function('random_between', 2, random.uniform)

results = db_conn.execute("SELECT random_between(0.3, 0.8)").fetchall()
print(results)

This avoids cluttering your SQL query with arithmetics and makes it look a lot more like what you were initially aiming for. It does require that you run the query via the C API or a language-specific binding to it, this cannot work as a "pure SQL" solution.

Need help adding random float in SQLite

3 Answers3