I would like to generate some UNIQUE random numbers in Snowflake with a specific starting/ending point. I would like for the numbers to start at 1,000 and end at 1,000,000.
Another requirement is joining a string at the beginning of these numbers.
So far I have been using this statement:
SELECT CONCAT('TEST-' , uniform(10000, 99000, RANDOM()));
Which works as expected and gives me the output of e.g. 'TEST-31633'.
However the problem is I am generating these for a large amount of rows, and I need for them to be completely unique.
I have heard of the 'SEQ1' functions however not sure how I could specify a starting point as well as adding a 'TEST-' with the CONCAT function at the beginning. Ideally they won't be in a strict sequence but differ from each other.
Thank You
It's not possible to guarantee uniqueness from a random number generator function unless you use some kind of calculated part. For example:
create or replace table testX as
select CONCAT('TEST-' , to_char(seq4(),'0000'),
uniform(100000, 990000, random())) c
from table(generator(rowcount => 1000));