SQL - generate alphanumeric string with specific format

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())
       );

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.

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

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;