CODEWITHSUNDEEP
sqlsql-serverrandomascii
Alycus
Alycus

Reputation: 221

SQL - generate alphanumeric string with specific format

I need to find out how to generate an alphanumeric string that follows the format like in the answer for this question which I'm currently using, except it has to be in the following format:

Vowel + consonant + vowel + consonant + 4-digit number

For example ABAB1111 or IJUZ9236.

Thanks for any suggestion.

Upvotes: 3

Views: 2065

Answers (5)

Sean Pearce
Sean Pearce

Reputation: 1169

DECLARE @AlphaString VARCHAR(200) = NULL;

WITH
    CTE_Digits AS (
    SELECT TOP 255
        ROW_NUMBER() OVER (ORDER BY a.object_id) AS RowNum
    FROM
        sys.all_columns a
    CROSS JOIN
        sys.all_columns b),
    CTE_Types AS (
    SELECT
        CHAR(RowNum) AS Digit,
        CASE
            WHEN RowNum < 58 THEN 'D'
            WHEN CHAR(RowNum) IN ('A','E','I','O','U') THEN 'V'
            ELSE 'C'
        END AS CharType
    FROM
        CTE_Digits
    WHERE
        RowNum BETWEEN 48 AND 57
        OR RowNum BETWEEN 65 AND 90),
    CTE_List AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY CharType ORDER BY NEWID()) AS NewRow
    FROM
        CTE_Types),
    CTE_Ordered AS (
    SELECT
        *,
        CASE CharType
            WHEN 'V' THEN 2
            WHEN 'C' THEN 3
            WHEN 'D' THEN 7
        END * NewRow AS DigitOrder
    FROM
        CTE_List
    WHERE
        (NewRow < 5
        AND CharType = 'D')
        OR NewRow < 3)
SELECT @AlphaString = 
(SELECT
    CAST(Digit AS VARCHAR(MAX))
FROM
    CTE_Ordered
ORDER BY
    DigitOrder
FOR XML PATH(''));

SELECT @AlphaString;

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44326

This should do the trick:

WITH letters as
(
  SELECT 'bcdfghjklmnpqrstvwxyz' c, 'aeiou' v
)
,CTE as
(
  SELECT
    SUBSTRING(v, CAST(rand()*5 as int)+1, 1)+
    SUBSTRING(c, CAST(rand()*21 as int)+1, 1)+
    SUBSTRING(v, CAST(rand()*5 as int)+1, 1)+
    SUBSTRING(c, CAST(rand()*21 as int)+1, 1)+
    right(10000+ CAST(rand()*10000 as int),4) x
  FROM letters
)
SELECT x
FROM CTE

Upvotes: 1

Rich Benner
Rich Benner

Reputation: 8113

Using temp tables as example data i'd do it like this;

CREATE TABLE #Vowels (Vowel varchar(1))
INSERT INTO #Vowels VALUES ('A'),('E'),('I'),('O'),('U')

CREATE TABLE #Consonants (Consonant varchar(1))
INSERT INTO #Consonants VALUES ('B'),('C'),('D'),('F'),('G'),('H'),('J'),('K'),('L'),('M'),('N'),('P'),('Q'),('R'),('S'),('T'),('V'),('W'),('X'),('Y'),('Z')

CREATE TABLE #Numbers (Numbers varchar(1))
INSERT INTO #Numbers VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)

SELECT
v1.Vowel + c1.Consonant + v2.Vowel + c2.Consonant + n1.Numbers + n2.Numbers + n3.Numbers + n4.Numbers AS Result

FROM (SELECT TOP 1 Vowel FROM #Vowels ORDER BY NEWID()) v1
CROSS JOIN (SELECT TOP 1 Consonant FROM #Consonants ORDER BY NEWID()) c1
CROSS JOIN (SELECT TOP 1 Vowel FROM #Vowels ORDER BY NEWID()) v2
CROSS JOIN (SELECT TOP 1 Consonant FROM #Consonants ORDER BY NEWID()) c2
CROSS JOIN (SELECT TOP 1 Numbers FROM #Numbers ORDER BY NEWID()) n1
CROSS JOIN (SELECT TOP 1 Numbers FROM #Numbers ORDER BY NEWID()) n2
CROSS JOIN (SELECT TOP 1 Numbers FROM #Numbers ORDER BY NEWID()) n3
CROSS JOIN (SELECT TOP 1 Numbers FROM #Numbers ORDER BY NEWID()) n4

DROP TABLE #Consonants
DROP TABLE #Numbers
DROP TABLE #Vowels

The result comes out like this but with different values each time you run it.

Result
AQOF7641

If you are running this a number of times, it would make sense to make proper tables containing your vowels, consonants and number. It would reduce the (admittedly small) cost of this query.

Upvotes: 1

sagi
sagi

Reputation: 40481

You can follow this steps:

Generate a vowels(A,E...) table , consonants (B,C..) table and numbers (1,2,..) table .

Then use this query:

SELECT  (SELECT TOP 1 * FROM vowels ORDER BY newid()) +
        (SELECT TOP 1 * FROM consonants ORDER BY newid()) +
        (SELECT TOP 1 * FROM vowels ORDER BY newid()) +
        (SELECT TOP 1 * FROM consonants ORDER BY newid()) +
        (SELECT TOP 1 * FROM numbers ORDER BY newid()) +
        (SELECT TOP 1 * FROM numbers ORDER BY newid()) +
        (SELECT TOP 1 * FROM numbers ORDER BY newid()) +
        (SELECT TOP 1 * FROM numbers ORDER BY newid())

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I assume you want a random string. Something like this should work:

with v as (
      select 'A' as c union all select 'E' union all . . . 
     ),
     c as (
      select 'B' as c union all select 'C' union all . . .
     ),
     d as (
      select '0' as c union all select '1' union all . . .
     )
select ((select top 1 c from v order by newid()) +
        (select top 1 c from c order by newid()) +
        (select top 1 c from v order by newid()) +
        (select top 1 c from c order by newid()) +
        (select top 1 c from d order by newid()) +
        (select top 1 c from d order by newid()) +
        (select top 1 c from d order by newid()) +
        (select top 1 c from d order by newid())
       );

Upvotes: 2

Related Questions