"Array" usage in T-SQL

Question

Trying to create a temp table where the AlphaSeq column would go through an "array" of alphanumeric characters that do not repeat and follow a flow as shown in the example below.

Example:

AlphaSeq	NumericSeq
A	1
A1	2
B	3
B1	4
AA	5
AB	6
BA	7
BB	8
[ETC]	[ETC]

Here's what I have so far (Obviously the "IG" is static as it is written now, so Im trying to make that field more fluid)

CREATE TABLE #Index 
(
     AlphaSeq VARCHAR(2), 
     NumericSeq TINYINT
)

DECLARE @ArrayNum AS TINYINT;
DECLARE @ReceiptSeq AS VARCHAR(2);

SET @ArrayNum = 0
SET @ReceiptSeq = 'IG'

WHILE @ArrayNum < 100
BEGIN 
    SET @ArrayNum = @ArrayNum + 1

    INSERT INTO #Index (AlphaSeq, NumericSeq)
    VALUES (@ReceiptSeq, @ArrayNum)
END

SELECT *
FROM #Index

I would suggest you get a list of all the charcters you need, and CROSS JOIN them to each other. — Thom A
– Thom A ♦, Commented Aug 25, 2021 at 18:01

Charlieface · Accepted Answer · 2021-08-25 18:40:03Z

1

You can simply cross-join the list of characters

CREATE TABLE #Index 
(
     AlphaSeq VARCHAR(2) NOT NULL, 
     NumericSeq SMALLINT NOT NULL IDENTITY
);

WITH Chars AS (
    SELECT *
    FROM (VALUES
      ('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z')
    ) v(chr)
),
CharsWithNums AS (
    SELECT *
    FROM (VALUES
      ('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('0')
    ) v(chr)
    UNION ALL
    SELECT *
    FROM Chars
)
INSERT #Index (AlphaSeq)
SELECT chr AS AlphaSeq
FROM Chars
UNION ALL
SELECT a.chr + b.chr
FROM Chars a
CROSS JOIN CharsWithNums b
ORDER BY AlphaSeq;

answered Aug 25, 2021 at 18:40

Charlieface

78.8k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

"Array" usage in T-SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related