Generate random id (fixed length) in T-SQL

Question

Possible Duplicate:
Generating random string in T-SQL

I need to convert this C# method in T-SQL:

 public static string GenerateRandomID(int size)
    {
        StringBuilder pass = new StringBuilder();
        Random random = new Random();
        for (int i = 0; i < size; i++)
        {
            int binary = random.Next(0, 2);
            switch (binary)
            {
                case 0:
                    char ch = (Convert.ToChar(Convert.ToInt32(Math.Floor(26 * random.NextDouble() + 65))));
                    pass.Append(ch);
                    break;
                case 1:
                    int num = random.Next(1, 10);
                    pass.Append(num);
                    break;
            }
        }
        return pass.ToString();
    }

Usage example: string output = GenerateRandomID(15)

output should look like: O1REGVIDK7T4R9R

Someone have an idea...

select char(rand()*26+65)+char(rand()*26+65)+char(rand()*26+65) +char(rand()*26+65)+char(rand()*26+65)+char(rand()*26+65) +char(rand()*26+65)+char(rand()*26+65)+char(rand()*26+65) +char(rand()*26+65)+char(rand()*26+65)+char(rand()*26+65) +char(rand()*26+65)+char(rand()*26+65)+char(rand()*26+65) — Maxime, Dec 19 '12 at 19:13
It work great but I need to mix aplha and numeric char in the id — Maxime, Dec 19 '12 at 19:14
Then why did you accept that answer to you prior question which was alpha numeric? — paparazzo, Dec 19 '12 at 19:20
I assume by the name having "ID" in it, you want to make sure there are no duplicates? — coge.soft, Dec 19 '12 at 20:36

score 1 · Accepted Answer · answered Dec 19 '12 at 19:44

create proc GenerateRandomID
    @size       int
as
begin

    declare @chars char(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    declare @i int = 0
    declare @id varchar(max) = ''

    while @i < @size
    begin
        if rand() > .5
            set @id = @id + substring(@chars, cast(ceiling(rand() * 26) as int), 1)
        else
            set @id = @id + cast(floor(rand() * 10) as varchar(2))
        set @i = @i + 1
    end

    select @id

end
go



exec GenerateRandomID 15

-------------------
BWZBKR601I8Z9KV

(1 row(s) affected)

Generate random id (fixed length) in T-SQL

1 Answers1