Generate random numbers, letters or characters within a range-CodePudding

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.

CodePudding user response：

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