I want to generate a random text value from a range and one that is not already present in the table.
For examle I have range from 'abcdef' to 'abcdej'.
I have a tableA(id varchar(6), someData)
where id must be unique.
I want to generate text value from range like from list ('abcdef','abcdeg','abcdeh','abcdei','abcdej') and check if it's not in tableA.
Is there any way to do it? (preferably without additional procedures)
Upd:
This is a bad solution to the problem because it will take a very long time and eat up a lot of resources, but if you have a similar problem on a smaller scale, this may suit you:
WITH RECURSIVE Tuple AS
(
SELECT
CAST(charr AS Text) AS Tuple
,1 AS TupleLength
FROM (select regexp_split_to_table('QWERTYUIOPASDFGHJKLZXCVBNM1234567890', '') charr) all_chars
UNION ALL
SELECT
T.Tuple || E.charr
,TupleLength + 1
FROM Tuple T
INNER JOIN (select regexp_split_to_table('QWERTYUIOPASDFGHJKLZXCVBNM1234567890', '') charr) E ON E.charr >= RIGHT(T.Tuple,1)
WHERE TupleLength < :length
)
SELECT Tuple
FROM Tuple
where TupleLength = :length /* between leftrange and rightrange */ /* and not n table */
ORDER BY TupleLength desc,Tuple;
It remains only to choose a random value from the list.
Thanks Addell El-haddad.