[SOLVED] Generate random numbers, letters or characters within a range

Generate random numbers, letters or characters within a range

I'm in the middle of a data anonymization for SQL Server.

I have this 3 formulas that help me create what I want:

SELECT CHAR(cast((90 - 65) * rand() + 65 AS INTEGER)) -- only letters
SELECT CAST((128 - 48) * RAND() + 48 AS INTEGER) -- only numbers
SELECT CHAR(CAST((128 - 48) * RAND() + 48 AS INTEGER)) -- letters, numbers, symbols

However, this only can create 1 number or 1 letter or 1 symbol.

I want to have the freedom that allows me to create a random string or number of the length I want. Like 3 or 5 numbers, 3 or 5 letters, 3 or 5 between numbers, letters or symbols.

I also have found something very close to what I want:

SELECT LEFT(CAST(NEWID() AS VARCHAR(100)), 3) -- letters and numbers

this is a very smart idea because uses NEWID() and it allows me to create a random sequence of numbers and letters of the length I want (3 in this case). But symbols are missing.

I need 3 different SELECT:

One for numbers only
One for letters only
One for numbers, letters and symbols

With the freedom of choice about the length of the data.

Solution

Some work required for a complete solution but here's the workings of an idea you might want to experiment with further, if you still need it:

declare @type varchar(10)='letters', @length tinyint=5;

with chars as (
    select top(59) 31 + Row_Number() over (order by (select 1)) n from master.dbo.spt_values
), s  as (
    select top (@length) Char(n.n) c
    from chars n
    where @type='all' 
    or (@type='symbols' and n between 33 and 47)
    or (@type='numbers' and n between 48 and 57)
    or (@type='letters' and n between 65 and 90)
    order by newid()
)
select String_Agg(s.c,'') 
from s