Given Letter, Get Next Letter in Alphabet

Question

I have letter "a", "b", "c". I would like my results to be "b", "c", "d" in TSQL respectively. Would what I use to achieve this?

Solution 1

Use ASCII to get the value of the character, add one, and use CHAR to convert the value back to a character.

SELECT CHAR(ASCII('a') + 1)

Solution 2

This is how you'd do it for one letter

DECLARE @myletter char(1) = 'a';
SELECT CHAR(ASCII(@myletter)+1);

Solution 3

3 letters alfabetic counter with two SQL procedures

(a, b...z, aa, ab...zy, zz, aaa...zzz):

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

alter procedure letraMas
@letraEntra as char( 1),
@letraSale as char( 1) OUTPUT,
@seLleva as bit  OUTPUT

as

--set @letraEntra = 'w'
--set @letraSale = 'm'
set @seLleva = 0

select
@letraSale = CASE
        WHEN @letraEntra = '' or (@letraEntra is null) or @letraEntra = 'z'
        THEN 'a'
    WHEN @letraEntra < 'z'
        THEN CHAR (ASCII( @letraEntra) + 1)
end

if @letraEntra = 'z' set @seLleva = 1

return

---

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/* USO:
declare
@tareaEntra as char(3),
@tareaSale as char(3)
set @tareaEntra = 'xzz'
       EXEC tareaMas @tareaEntra, @tareaSale OUTPUT
*/
alter procedure tareaMas
@tareaEntra as char( 3),
@tareaSale as char( 3) OUTPUT

as
declare @charU as char(1 )     -- char de U_nidades albabéticas
declare @charD as char(1 )     -- char de D_ecenas albabéticas
declare @charC as char(1 )     -- char de C_entenas albabéticas
declare @letraSale as char(1 )        -- char de C_entenas albabéticas
declare @seLleva as bit

set @tareaEntra   = right('   ' + rtrim (@tareaEntra), 3)

set @charU = substring(@tareaEntra , 3, 1)
set @charD = substring(@tareaEntra , 2, 1)
set @charC = substring(@tareaEntra , 1, 1)

EXEC letraMas @charU, @letraSale OUTPUT, @seLleva OUTPUT
set @charU = @letraSale
if @seLleva = 1
BEGIN
        EXEC letraMas @charD, @letraSale OUTPUT, @seLleva OUTPUT
        set @charD = @letraSale
        if @seLleva = 1
        BEGIN
               EXEC letraMas @charC, @letraSale OUTPUT, @seLleva OUTPUT
               set @charC = @letraSale
        END
END

set @tareaSale = ltrim(@charC + @charD + @charU)
return

Solution 4

Here is a CTE with Jonathan Wood's implementation

;WITH cte AS
   (SELECT  CHAR(ASCII('a')) [char], 1 [count]
    UNION ALL
    SELECT  CHAR(ASCII('a') + cte.count) [char], cte.count + 1 [count]
    FROM    cte)
SELECT  TOP(26) cte.count[pos], cte.char
FROM    cte

you can either use it just like that or insert the results into a table variable or temporary table and use it on that.

Another tip I would also give is, have a table in your database with this cte's data, then it is easier in the future to join to it and use it like that for whatever purpose or reason.

View more solutions