CODEWITHSUNDEEP
sqliterandom
SJones
SJones

Reputation: 59

Need help adding random float in SQLite

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.

Upvotes: 3

Views: 2081

Answers (3)

Santo Guevarra
Santo Guevarra

Reputation: 415

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.

Upvotes: 3

Mumbleskates
Mumbleskates

Reputation: 1318

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.

Upvotes: 7

CL.
CL.

Reputation: 180080

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;

Upvotes: 2

Related Questions