给定字母，获取字母表中的下一个字母

Question

我有字母"a"、"b"、"c"。我希望我的结果在TSQL中分别为"b"、"c"one_answers"d"。我会用什么来实现这一点吗？

Answer 1

使用ASCII获取字符的值，加一，然后使用CHAR将值转换回字符。

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

Answer 2

这是对一个字母的处理方法

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

Answer 3

3个字母的带有两个SQL过程的辅助计数器

（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

Answer 4

以下是Jonathan Wood实现的CTE

;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

您可以像那样使用它，也可以将结果插入到表变量或临时表中并在其中使用它。

我还想给你的另一个提示是，在你的数据库中有一个包含这个cte数据的表，然后在未来更容易加入它，并出于任何目的或原因这样使用它。

Answer 5

DECLARE @Letters AS TABLE
(
    Letter CHAR(1)
)
INSERT INTO @Letters
        ( Letter )
VALUES  ( 'A' )
INSERT INTO @Letters
        ( Letter )
VALUES  ( 'B' )
INSERT INTO @Letters
        ( Letter )
VALUES  ( 'C' )
SELECT CHAR(ASCII(Letter) + 1) FROM @Letters

在SQL中使用表变量的示例，但以上任何一项都可以为您完成。显然，这取决于你用什么方法整理字母表。