CODEWITHSUNDEEP
sqlsql-server
user3605493
user3605493

Reputation: 41

Auto string generator

Is it possible in SQL to generate random alphanumeric character string?

current strings are ‘Mxxxx’ and ‘Pxxxx’ where the xxxx is just a sequential number.

i need a format with alphanumeric character in position 1, 2 & 4, special characters in position 3 & 5. All will be random and unique.

The alphanumeric characters are A – Z, 1 – 9. The special characters are *, +, =, #, /, %, &, !, and ?.

is it possible to generate a list of 400 using this format in sql server?

Thanks

Upvotes: 1

Views: 245

Answers (3)

serge
serge

Reputation: 1022

Have a look at my blog post about methods to generate random strings using the pure TSQL: "SQL: generate random character string". The second method doesn't have any string length limitations.

Upvotes: 1

Alex Yu
Alex Yu

Reputation: 3547

You can do something like this: 

;WITH ALPHA AS (
SELECT DISTINCT CHAR(object_id) AS C FROM sys.columns SC 
   WHERE 
     object_id BETWEEN ASCII('A') AND ASCII('Z')
     OR object_id BETWEEN  ASCII('0')  AND  ASCII('9')
), SPC AS(
SELECT DISTINCT CHAR(object_id) AS S FROM sys.columns SC 
    WHERE 
      object_id IN (ASCII('*'), ASCII('+'), ASCII('='), ASCII('#'),     ASCII('/'), ASCII('%'), ASCII('&'), ASCII('!'))    
)
SELECT TOP 10 A1.C + A2.C + A3.C + S1.S + S2.S + S3.S
   FROM 
      ALPHA A1, ALPHA A2, ALPHA A3, SPC S1, SPC S2, SPC S3
   ORDER BY NEWID()

Try it on rextester: stackoverflow-54809150-auto-string-generator

Adjust quantity and position of symbols as you need.

Beware:

It is quite resource consuming! If you need to run it a lot of times - prepare table(s) with computed values and select from them.

Upvotes: 0

Dheerendra
Dheerendra

Reputation: 308

You can use something like on the below lines.

DECLARE @AlLChars varchar(100) = 'ABCDEFGHIJKL'
DECLARE @Numerics varchar(100) = '12345678910'
DECLARE @SpecialChars varchar(10) = '*+=#/%&!?'
DECLARE @I INT = 1
WHILE @I <= 400
BEGIN
INSERT INTO tblStrings 
SELECT 
 RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%26) + 1 ),1) 
+RIGHT( LEFT(@Numeric,ABS(BINARY_CHECKSUM(NEWID())%10) + 1 ),1) 
+RIGHT( LEFT(@SpecialChars,ABS(BINARY_CHECKSUM(NEWID())%9) + 1 ),1) 
+RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%26) + 1 ),1) 
+RIGHT( LEFT(@SpecialChars,ABS(BINARY_CHECKSUM(NEWID())%9) + 1 ),1) 
SET @I = @I + 1
END;

Upvotes: 0

Related Questions