1

Topic: Generation of the unique casual codes

Good afternoon.
The question certainly not absolutely approaches to a forum, but to write most likely  on T-SQL.
It is necessary to generate the unique casual code, which length from 1. 20, each sign is value from 0-9 and a-z.
The code should be unique within a class where the class is length of the code:
Class  - 1NM, 5VF....
Class XX - 1NMGH, 5VFJU....
I.e. only 20 classes of odes  to
The amount of the unique codes in  is not less 1.000.0000 and further without restrictions (it is finite if allows the size of a class).
While on mind the decision on the basis of the table (a class | the code | is used) comes. Into the table will be interposed  the code with check of its uniqueness on (a class | the code). But generation rate will be terribly low.
Who can faced with similar .

2

Re: Generation of the unique casual codes

mezzanine wrote:

But generation rate will be terribly low.

And how many millions a second are necessary ?

3

Re: Generation of the unique casual codes

Most of all plan to generate packs for 100.000 but also 5 minutes on this operation will be  time. It will be at first bright enough. But the more  records in a class, the longer following packs.

4

Re: Generation of the unique casual codes

100. For some

/*
create table t
(
class tinyint NOT NULL;
val varchar (20) NOT NULL
PRIMARY KEY (class, val)
)
*/
declare @count_gen int = 100000
declare @class int=4
--------------
declare @VAL char (36) = ' 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
declare @rezul table (val varchar (20))
declare @exists decimal (38,0) = (select count (*) from t where class = class)
set @count_gen = case
when @count_gen> power (cast (36 as decimal (38,0)) ,@class) -@exists
then power (cast (36 as decimal (38,0)) ,@class) -@exists
else @count_gen
end
while @count_gen> 0
begin
insert into t (class, val)
output inserted.val into @rezul (val)
select top (@count_gen) @class, z.val
from
(
select distinct z.val from
(
select
LEFT (substring (@VAL, 1+ASCII (substring (Gen, 1,1)) %36,1) +
substring (@VAL, 1+ASCII (substring (Gen, 2,1)) %36,1) +
substring (@VAL, 1+ASCII (substring (Gen, 3,1)) %36,1) +
substring (@VAL, 1+ASCII (substring (Gen, 4,1)) %36,1) +
substring (@VAL, 1+ASCII (substring (Gen, 5,1)) %36,1) +
substring (@VAL, 1+ASCII (substring (Gen, 6,1)) %36,1) +
substring (@VAL, 1+ASCII (substring (Gen, 7,1)) %36,1) +
substring (@VAL, 1+ASCII (substring (Gen, 8,1)) %36,1) +
substring (@VAL, 1+ASCII (substring (Gen, 9,1)) %36,1) +
substring (@VAL, 1+ASCII (substring (Gen, 10,1)) %36,1) +
substring (@VAL, 1+ASCII (substring (Gen, 11,1)) %36,1) +
substring (@VAL, 1+ASCII (substring (Gen, 12,1)) %36,1) +
substring (@VAL, 1+ASCII (substring (Gen, 13,1)) %36,1) +
substring (@VAL, 1+ASCII (substring (Gen, 14,1)) %36,1) +
substring (@VAL, 1+ASCII (substring (Gen, 15,1)) %36,1) +
substring (@VAL, 1+ASCII (substring (Gen, 16,1)) %36,1) +
substring (@VAL, 1+ASCII (substring (Gen, 17,1)) %36,1) +
substring (@VAL, 1+ASCII (substring (Gen, 18,1)) %36,1) +
substring (@VAL, 1+ASCII (substring (Gen, 19,1)) %36,1) +
substring (@VAL, 1+ASCII (substring (Gen, 20,1)) %36,1) ,@class) val
from
(
select cast (HashBytes (' SHA1 ', cast (NEWID () as char (36))) as char (20)) Gen
from master.dbo.spt_values v
) z
) z
left join t
on t.class = class and t.val=z.val
where t.class is null
) z
set @count_gen = @COUNT_GEN - @@ ROWCOUNT
end
select * from @rezul

5

Re: Generation of the unique casual codes

) z
set @count_gen = @COUNT_GEN - @@ ROWCOUNT

To replace on

) z
order by NEWID ()
set @count_gen = @COUNT_GEN - @@ ROWCOUNT

So it turns out not small ranges more casually

6

Re: Generation of the unique casual codes

For optimization still it is possible to make a trace. To the logician:
If @@ rowcount returned less than 300 lines that to finish to 300 by means of emptiness from the table t.
Quickly to find emptiness it is necessary will enter one more column NextVallExists bit in the table t which 1 if following number in sequence = +1 (1.AA 2.AB). Calculation NextVallExists to lead after each generation proceeding from the table @rezult.

7

Re: Generation of the unique casual codes

(select count (*) from t where class = class)

Here still it is necessary to replace it, since will long consider. Will make the separate table in which to contain total for each class. Or each class to break into separate partitions and by means of system tables quickly to find total for each class (select partition_id, rows from sys.partitions)

8

Re: Generation of the unique casual codes

mezzanine wrote:

Good afternoon.
The question certainly not absolutely approaches to a forum, but to write most likely  on T-SQL.
It is necessary to generate the unique casual code, which length from 1. 20, each sign is value from 0-9 and a-z.
The code should be unique within a class where the class is length of the code:
Class  - 1NM, 5VF....
Class XX - 1NMGH, 5VFJU....
I.e. only 20 classes of odes  to
The amount of the unique codes in  is not less 1.000.0000 and further without restrictions (it is finite if allows the size of a class).
While on mind the decision on the basis of the table (a class | the code | is used) comes. Into the table will be interposed  the code with check of its uniqueness on (a class | the code). But generation rate will be terribly low.
Who can faced with similar .

1. The most interesting in this invented problem:   "casual"?
As soon as he realizes senselessness of this "requirement" - all becomes simple and fast.
2. If   "casual" are necessary, blood from a nose. That preliminary generation of the table with a store "for hundred years forward" solves also this far-fetched problem.

9

Re: Generation of the unique casual codes

aleks222;
1. Will be used different , at length of the code of 20 signs it will be difficult to give a sequence though something similar to randomness.
2. Just considered this variant. Thanks LoopN.

10

Re: Generation of the unique casual codes

mezzanine, still here such variant.

declare @count_gen int = 100000;
declare @class int = 4;
select top (@count_gen)
(select c as [text ()]
from (select top (@class) char (number) as c
from master. spt_values sv
where sv. Type = ' P ' and (sv.number between 48 and 57 or number between 97 and 122)
order by sv1.Name, sv2.Name, newid ()) as t2
for xml path ("))
from master. spt_values as sv1
cross join master. spt_values as sv2

11

Re: Generation of the unique casual codes

/ ******
* generate random PIN word with next criteria:
* length between 6 and 9 symbols
* word must have: number, alphobetic symbols in lower and upper case
* INPUT PARAMETERS:
*
* @@pattern INT - Pin Code Patterns
* a - Alpha (a-z) case insensitive compare
* A - mixed case Alpha (A-Z, a-z)
* N - Numeric (0-9)
* M - AlphaNumeric (0-9, a-z) case insensitive compare
* M - Mixed case Alpha Numeric (0-9, A-Z, a-z)
* Usage: SELECT dbo.fn_gen_pin (' MMMMMMMMMMMMMMMM ') UNION ALL SELECT dbo.fn_gen_pin (' AAAAAAAAAAAAAAAA ') UNION ALL SELECT dbo.fn_gen_pin (' NNNNNNNNN ')
SELECT dbo.fn_gen_pin (' mmmmmmm ')
*
* Return: user pin code, for example: cv90Fy21
*/
CREATE FUNCTION [dbo]. [fn_gen_pin] (@pattern varchar (32))
RETURNS varchar (16)
WITH SCHEMABINDING
AS
BEGIN
DECLARE @result varchar (16)
IF @pattern IS NULL OR LEN (@pattern) <6 OR LEN (@pattern)> 16
RETURN ' Error ';
WITH cte AS
(
SELECT TOP (60) c1 AS sym FROM
(
SELECT c1, 1 AS code FROM
(SELECT TOP 24 c1 FROM (VALUES (' A '), (' B '), (' A C '), (' D '), (' E '), (' F '), (' G '), (' H '), (' J '), (' K '), (' L '), (' M '), (' N '), (' P '), (' Q '), (' R '), (' S'), ('T '), (' U '), (' V '), (' W '), (' X '), (' Y '), (' Z ')) AS T1 (c1)
ORDER BY (SELECT rndOrder FROM dbo.vw_orderby)) j
UNION ALL
SELECT c2, 2 AS code FROM
(SELECT TOP 23 c2 FROM (VALUES (' a '), (' b '), (' c '), (' d '), (' e '), (' f '), (' g '), (' h '), (' j '), (' k '), (' m '), (' n '), (' p '), (' q '), (' r '), (' s'), ('t '), (' u '), (' v '), (' w '), (' x '), (' y '), (' z ')) AS T2 (c2)
ORDER BY (SELECT rndOrder FROM dbo.vw_orderby)) k
UNION ALL
SELECT c3, 4 AS code FROM
(SELECT TOP 23 c3 FROM (VALUES (' 0 '), (' 1 '), (' 2 '), (' 3 '), (' 4 '), (' 5 '), (' 6 '), (' 7 '), (' 8 '), (' 9 ')) AS T3 (c3)
ORDER BY (SELECT rndOrder FROM dbo.vw_orderby)) l
)x WHERE code
& (CASE
WHEN ASCII (LEFT (@pattern, 1)) = ASCII (' a ') THEN 1
WHEN ASCII (LEFT (@pattern, 1)) = ASCII (' A ') THEN (1 | 2)
WHEN ASCII (LEFT (@pattern, 1)) = ASCII (' N ') THEN 4
WHEN ASCII (LEFT (@pattern, 1)) = ASCII (' m ') THEN (4 | 2)
WHEN ASCII (LEFT (@pattern, 1)) = ASCII (' M ') THEN (1 | 2 | 4) END
) = code
ORDER BY (SELECT rndOrder FROM dbo.vw_orderby)
)
SELECT @result = LEFT (result, LEN (@pattern)) FROM
(
SELECT
(
SELECT sym AS [text ()] FROM
(
SELECT TOP 16 sym FROM
(
SELECT TOP 6 sym FROM
(
SELECT TOP 1 sym, (select 1 + CAST (rndVal AS int) % 3 from dbo.vw_rnd) AS ord FROM cte WHERE UNICODE (sym) BETWEEN 97 AND 122
UNION ALL
SELECT TOP 1 sym, (select 1 + CAST (rndVal AS int) % 3 from dbo.vw_rnd) AS ord FROM cte WHERE UNICODE (sym) BETWEEN 65 AND 90
UNION ALL
SELECT TOP 1 sym, (select 1 + CAST (rndVal AS int) % 3 from dbo.vw_rnd) AS ord FROM cte WHERE UNICODE (sym) BETWEEN 48 AND 57
UNION ALL
SELECT TOP 1 sym, (select 3 + CAST (rndVal AS int) % 3 from dbo.vw_rnd) AS ord FROM cte WHERE UNICODE (sym) BETWEEN 97 AND 122
UNION ALL
SELECT TOP 1 sym, (select 3 + CAST (rndVal AS int) % 3 from dbo.vw_rnd) AS ord FROM cte WHERE UNICODE (sym) BETWEEN 65 AND 90
UNION ALL
SELECT TOP 1 sym, (select 3 + CAST (rndVal AS int) % 3 from dbo.vw_rnd) AS ord FROM cte WHERE UNICODE (sym) BETWEEN 48 AND 57
)sts ORDER BY ord ASC
UNION ALL
SELECT sym FROM cte
)z
) AS T2 FOR XML PATH (")
) AS result
)x
RETURN @result;
END
GO

12

Re: Generation of the unique casual codes

Forgot to add
CREATE VIEW [dbo]. [vw_rnd]
WITH SCHEMABINDING
AS
SELECT CAST (round (rand () * 10 + 1, 0) AS INT) AS rndVal
GO

13

Re: Generation of the unique casual codes

And still
CREATE VIEW [dbo]. [vw_orderby]
WITH SCHEMABINDING
AS
SELECT ABS (CAST (round (rand () * 100000000 + 1, 0) AS BIGINT) ^ CHECKSUM (CAST (getdate () AS BINARY (19))) ^ CHECKSUM (newidVal)) AS rndOrder
FROM dbo.vw_NEWID, dbo.vw_rnd
GO

14

Re: Generation of the unique casual codes

CREATE VIEW [dbo]. [vw_NEWID]
WITH SCHEMABINDING
AS
SELECT CAST (CAST (NEWID () AS varchar (36)) AS varchar (36)) AS newidVal
GO

15

Re: Generation of the unique casual codes

2Lepsik
[spoiler]

/*
* ...
* word must have: number, alph>>> A <<<betic symbols in lower and upper case
*/

[/spoiler]

16

Re: Generation of the unique casual codes

Gavrilenko Sergey Alekseevich;
M - AlphaNumeric (0-9, a-z) case insensitive compare
SELECT dbo.fn_gen_pin (' mmmmmmm ')

17

Re: Generation of the unique casual codes

Again not that, here is true
SELECT dbo.fn_gen_pin (' MMMMMMMMMMMMM ')
0HsQ9uUtkNT2M

18

Re: Generation of the unique casual codes

Lepsik wrote:

again not that, here is true
SELECT dbo.fn_gen_pin (' MMMMMMMMMMMMM ')
0HsQ9uUtkNT2M

What for this product of a sick brain?
Regular RAND () consults.

ALTER FUNCTION [dbo]. [BigIntToStr] (@i bigint, @Digits varchar (1024))
RETURNS varchar (64)
as
begin
declare @base int, @result varchar (64);
set @base = LEN (@Digits);
with digits as (select D = SUBSTRING (@Digits, @i % base + 1, 1), i = @i / base, n = 1
UNION ALL
select D = SUBSTRING (@Digits, i % base+1, 1), i = i / base, n = n + 1
FROM digits WHERE i> 0
)
select @result = (select D + "FROM digits ORDER BY n ASC for xml path ("));
return @result;
end
go
-- I hope to guess to transfer a line of the necessary characters and to cause some times if it is necessary long result not too difficult?
select dbo. BigIntToStr (9223372036854775807*rand (), ' 0123456789abcdefghABCDE ')
select dbo. BigIntToStr (9223372036854775807*rand (), ' 0123456789 ')

19

Re: Generation of the unique casual codes

Well and for greater high-speed performance - it is possible inline-function

ALTER FUNCTION [dbo]. [BigIntToStr] (@i bigint, @Digits varchar (1024))
RETURNS TABLE
AS
RETURN
(
with b as (select base = len (@Digits))
, d as (select d = substring (@Digits, @i%base + 1, 1), i = @i/base, n = 1 from b
UNION ALL
select d = substring (@Digits, i%base + 1, 1), i = i/base, n = n + 1
from d inner join b on 1 = 1 where i> 0
)
select str = (select d + "from d order by n asc for xml path ("))
)