CODEWITHSUNDEEP
sqlsql-server
PriceCheaperton
PriceCheaperton

Reputation: 5369

create a list of the alphabet via SQL

I would like to produce results from the alphabet via SQL?

Something like this:

A
B
C
D
E
F

I have tried:

SELECT 
'A','B','C'

But this just produces the letters across in columns.

Upvotes: 3

Views: 23948

Answers (12)

DS Labs
DS Labs

Reputation: 11

If your using SQL Server 2022 you can use GENERATE_SERIES in a few ways:

-- Shortest syntax assuming CHAR(65) as A and CHAR(90) AS Z:
SELECT  CHAR(value) Letter 
FROM    GENERATE_SERIES(65, 90);

-- For lowercase use 97-122 or wrap in UPPER/LOWER
SELECT  CHAR(value) Letter 
FROM    GENERATE_SERIES(97, 122);

-- Easier for a user to understand using the numerical positions of the alphabet:
SELECT  CHAR(64 + value) Alphabet
FROM    GENERATE_SERIES(1, 26, 1);

-- Aggregate all letters together in a single string by combining with STRING_AGG
SELECT  STRING_AGG(CHAR(64 + value),'') Alphabet_Aggregated
FROM    GENERATE_SERIES(1, 26, 1);

-- Previous version will work 99.99999% of the time but technically needs to be sorted:
SELECT  STRING_AGG(CHAR(64 + value),'') WITHIN GROUP (ORDER BY value ASC) AS Alphabet_Sorted
FROM    GENERATE_SERIES(1, 26, 1);

Upvotes: 1

Doug
Doug

Reputation: 1

You can do it this way and not worry about a table on 26 length. See here for more details.

SELECT CHAR(value) alpha FROM GENERATE_SERIES(65, 90)

Upvotes: 0

Amber G
Amber G

Reputation: 1

SELECT 'A' letter
UNION ALL
SELECT 'B' letter
UNION ALL
SELECT 'C' letter
UNION ALL
SELECT 'D' letter
UNION ALL
SELECT 'E' letter
UNION ALL
SELECT 'F' letter
UNION ALL
SELECT 'G' letter
UNION ALL
SELECT 'H' letter
UNION ALL
SELECT 'I' letter
UNION ALL
SELECT 'J' letter
UNION ALL
SELECT 'K' letter
UNION ALL
SELECT 'L' letter
UNION ALL
SELECT 'M' letter
UNION ALL
SELECT 'N' letter
UNION ALL
SELECT 'O' letter
UNION ALL
SELECT 'P' letter
UNION ALL
SELECT 'Q' letter
UNION ALL
SELECT 'R' letter
UNION ALL
SELECT 'S' letter
UNION ALL
SELECT 'T' letter
UNION ALL
SELECT 'U' letter
UNION ALL
SELECT 'V' letter
UNION ALL
SELECT 'W' letter
UNION ALL
SELECT 'X' letter
UNION ALL
SELECT 'Y' letter
UNION ALL
SELECT 'Z' letter;

Upvotes: 0

Abel Wenning
Abel Wenning

Reputation: 651

Stemming from @MarkoJuvančič's answer, but a solution that will work on every SQL DBMS:

CREATE TEMPORARY TABLE alphabet (ltr CHAR(1));

SET @row_number = 0;

INSERT INTO alphabet
SELECT
        CHAR((@row_number:=@row_number + 1) +64) -- 'A' is the 65th character on the ASCII table
    FROM customer -- any table with 26 or more rows could suffice for this job
    WHERE @row_number < 26;

Upvotes: 0

Pranto Das
Pranto Das

Reputation: 1

Try this

;WITH CHARA2Z
AS (
    SELECT 
        [ASCII] = ASCII('A'), 
        [LETTER] = CHAR(ASCII('A'))
    
    UNION ALL

    SELECT 
        [ASCII] + 1, 
        [LETTER] = CHAR([ASCII]+1)
    FROM 
        CHARA2Z
    WHERE 
        [ASCII] < ASCII('Z')
)

SELECT * FROM CHARA2Z

Replace 'A' & 'Z' by 'a' & 'z' for small letters.

Upvotes: 0

Robert
Robert

Reputation: 11

with AlphabetList as
(
select  char(65) letter
union all
select  char(ascii(letter) + 1)
from    AlphabetList
where   letter <> 'Z'
)
select  *
from    AlphabetList

Upvotes: 1

Sean
Sean

Reputation: 39

Using a recursive CTE (common table expression) to output the alphabet, A-Z, one row per letter/character:

;WITH cteAZ AS
(
SELECT ASCII('A') [AlphaCode],CAST('A' AS CHAR(1)) [Alpha]
UNION ALL
SELECT a.AlphaCode + 1 [AlphaCode],CAST(CHAR(a.AlphaCode + 1) AS CHAR(1)) [Alpha]
FROM cteAZ a WHERE a.AlphaCode < ASCII('Z')
)
SELECT 
az.AlphaCode,az.Alpha
FROM 
cteAZ az

Upvotes: 0

Frank Ropen
Frank Ropen

Reputation: 99

You could use U-SQL

Select
    [letter]
From
    (
        Values
            ('A')
            ,('B')
            ,('C')
    ) As [Letters]([letter])

Upvotes: 1

Marko Juvančič
Marko Juvančič

Reputation: 5890

Use table spt_values and convert values to chars

SELECT Char(number+65) 
  FROM master.dbo.spt_values
 WHERE name IS NULL AND 
       number < 26

EDIT: This table is undocumented. But, it's used by many system storedprocedures and it's extremely unlikely for this table to disappear, since all those procs should be rewritten. This would be like poking a sleeping lion.

Upvotes: 12

CRAFTY DBA
CRAFTY DBA

Reputation: 14925

--
-- tally = 9466 rows in my db, select upper & lower alphas
-- 
;
with 
cte_tally as
(
select row_number() over (order by (select 1)) as n 
from sys.all_columns
)
select 
  char(n) as alpha
from 
  cte_tally
where
  (n > 64 and n < 91) or
  (n > 96 and n < 123);
go

The sys.all_columns is a documented table. It will be around for a while.

http://technet.microsoft.com/en-us/library/ms177522.aspx

It seems clear that the table, sp_values, is undocumented and can be removed in the future without any comment from Microsoft.

Upvotes: 7

Punter015
Punter015

Reputation: 1796

If you want to print from A to Z, then:

DECLARE @i int=65
WHILE @i < 91
BEGIN
PRINT CHAR(@i);
SET @i=@i+1;
END

Upvotes: 1

TechDo
TechDo

Reputation: 18659

Try:

select 'A' union
select 'B' union
select 'C'

Upvotes: 2

Related Questions