CODEWITHSUNDEEP
sqlrandominformix
famedoro
famedoro

Reputation: 1241

Is it possible to generate a unique random alphanumeric code consisting of 5 characters using Informix or standard sql?

I'm using informix IDS server, and I have this table table1 :

Column name          Type                                    Nulls

ad_code              char(5)                                 yes
ad_value             smallint                                yes

I would like to generate a random, unique code for field ad_code (and then store it in the ad_code field) of table table1 using sql. Is it possible ?

It would be enough, alternatively, an alphanumeric code sequnziale, es. 00001 ... 00009 ... 0000A unique

I have tried something like this, in the case of hexadecimal values in the field ad_code:

select  first 5  ('0x'||NVL( l.ad_code, '0'))::INT + 1
from table1 as l
left outer join table1 as r on
('0x'||NVL( l.ad_code, '0'))::INT + 1 =
('0x'||NVL( r.ad_code, '0'))::INT
where       
r.ad_code is null ORDER BY 1 ASC;
commit;

but is very slow despite having created an index on ad_code.

Upvotes: 1

Views: 513

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 754480

You can generate 15-bit random numbers using this pair of stored procedures:

-- @(#)$Id: random.spl,v 1.2 1997/12/08 19:31:44 johnl Exp $
--
-- Simple emulation of SRAND and RAND in SPL
-- Using random number generator suggested by C standard (ISO 9899:1990)

CREATE PROCEDURE sp_setseed(n INTEGER)
        DEFINE GLOBAL seed DECIMAL(10) DEFAULT 1;
        LET seed = n;
END PROCEDURE;

CREATE PROCEDURE sp_random() RETURNING INTEGER;
        DEFINE GLOBAL seed DECIMAL(10) DEFAULT 1;
        DEFINE d DECIMAL(20,0);
        LET d = (seed * 1103515245) + 12345;
        -- MOD function does not handle 20-digit values...  Dammit!!
        LET seed = d - 4294967296 * TRUNC(d / 4294967296);
        RETURN MOD(TRUNC(seed / 65536), 32768);
END PROCEDURE;

You can use those numbers to generate a sequence of alphanumeric characters, with more or less finesse. A simple approach uses the random number modulo the number of alphanumeric characters (do you want [A-Z0-9] or [a-z0-9] or [A-Za-z0-9], or something else), and select a character each time. There is potential for unequal distributions if you are not careful (because if there are 32768 possible random numbers, and you have 36 possible characters, 8 of characters will have a 911 out of 32768 chance of being selected, while the other 28 will only have a 910 out of 32768 chance of being selected (and the problem is bigger if you use 62 characters — lower-case, upper-case and digits). There are ways to deal with that if it is a problem.

Here's the simple-minded, slightly skewed approach at work:

-- @(#)$Id: randomstring.spl,v 1.1 2018/08/27 16:43:59 jonathanleffler Exp $
--
-- Generate a random sequence of characters from given list

CREATE FUNCTION sp_randomstring(str VARCHAR(255), num INTEGER)
    RETURNING VARCHAR(255) AS random_string;
    DEFINE r VARCHAR(255);
    DEFINE i INTEGER;
    DEFINE n INTEGER;
    DEFINE j INTEGER;
    LET r = "";
    LET n = LENGTH(str);
    FOR i = 1 TO num
        LET j = MOD(sp_random(), n) + 1;
        LET r = r || SUBSTR(str, j, 1);
    END FOR;
    RETURN r;
END FUNCTION;

EXECUTE FUNCTION sp_randomstring("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 20);
EXECUTE FUNCTION sp_randomstring("abcdefghijklmnopqrstuvwxyz0123456789", 21);
EXECUTE FUNCTION sp_randomstring("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 22);
EXECUTE FUNCTION sp_randomstring("abcdefghijklmnopqrstuvwxyz0123456789", 23);
EXECUTE FUNCTION sp_randomstring("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 24);
EXECUTE FUNCTION sp_randomstring("abcdefghijklmnopqrstuvwxyz0123456789", 24);
EXECUTE FUNCTION sp_randomstring("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 23);
EXECUTE FUNCTION sp_randomstring("abcdefghijklmnopqrstuvwxyz0123456789", 22);
EXECUTE FUNCTION sp_randomstring("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 21);
EXECUTE FUNCTION sp_randomstring("abcdefghijklmnopqrstuvwxyz0123456789", 20);

Sample output:

087TTLGDMSNXMAFL7PJG
cklp14dfk66308lxkzjyu
6JDTOJLC47UE9GWSHRBH55
gwpmrfwwwcykgqbn494bmeh
TNY3U3VMHN01UZS1GV4LOF0K
tm38v8qwqj6o0vrsh9gbb0w6
YF6QP6NT3VK5ARTFDL1N32B
vrb9pvww5cw6egsz9tniex
9OIY799Z694DBENBDFSFE
1gkj5adm3bswlo26wd5i

I didn't set the seed, so that code generates the same sequence every time. You might use a sequence to generate a new seed each time, or you might do something else to seed the generator. The seed is private to each session.

Upvotes: 2

Related Questions