sql-server SQL：是否有可能将数字(1、2、3、4 ...)转换为字母(A、B、C、D...)

klsxnrf1  于 2022-10-31  发布在  其他
关注(0)|答案(5)|浏览(192)

是否有可能获得字母（如A、B）而不是数字（1、2），例如作为Dense_Rank函数调用的结果（在MS Sql中）？

sql-server

5条答案

按热度按时间
xxb16uws

xxb16uws1#

不是一个直接的答案-但如果有人有一个字母转换的要求与3个字符，以下是我正在做的。

/*                    
Function Desc: Convert integer value to 3 character alpha-numeric
--Note: 1. This will return unique values from 0 to 17575, after that it startes again from AAA.
        2. Returns NULL If less than 0.

--Test Values
    select dbo.udfGetBase26CharacterValue(0) --AAA
    select dbo.udfGetBase26CharacterValue(17575) --ZZZ
    select dbo.udfGetBase26CharacterValue(17576) --AAA
    select dbo.udfGetBase26CharacterValue(NULL) --NULL
    select dbo.udfGetBase26CharacterValue(-1) --NULL

* /

CREATE FUNCTION [dbo].udfGetBase26CharacterValue
(    
    @id INT
)    
RETURNS CHAR(3)   
AS    
BEGIN    

IF ((@id < 0) OR (@id IS NULL))
BEGIN
    Return NULL
END

--Convert to base 26
Return  char(@id / power(26,2) % 26 + 65) +
            char(@id / 26 % 26 + 65) + 
            char(@id % 26 + 65)    

END

另一种方法-获取下一个字符代码（它是字母数字结果）。如果传递'00 A'，它将返回'00 B'

CREATE FUNCTION dbo.fnGetNextCharacterCode (@InputCode char(3))
RETURNS char(3)
AS
BEGIN

IF LEN(LTRIM(RTRIM(@InputCode))) = 2
BEGIN
    SET @InputCode = '0'+LTRIM(RTRIM(@InputCode))
END
ELSE IF LEN(LTRIM(RTRIM(@InputCode))) = 1
BEGIN
    SET @InputCode = '00'+LTRIM(RTRIM(@InputCode))
END

DECLARE @NewCode char(3)

SELECT @NewCode =
                    CASE WHEN RIGHT(@InputCode,2) != 'ZZ' THEN LEFT(@InputCode,1)
                       ELSE CHAR(
                                    CASE LEFT(@InputCode,1) WHEN '9' THEN 64 
                                                       WHEN 'Z' THEN 47 
                                                       ELSE ASCII(LEFT(@InputCode,1)
                                            ) 
                                  END + 1
                                )
                 END ---First Char
                 + 
                 CASE WHEN RIGHT(@InputCode,1) != 'Z' THEN SUBSTRING(@InputCode,2,1)
                       ELSE CHAR(
                                    CASE SUBSTRING(@InputCode,2,1) WHEN '9' THEN 64 
                                                       WHEN 'Z' THEN 47 
                                                       ELSE ASCII(SUBSTRING(@InputCode,2,1)) 
                                    END + 1
                                )
                   END ---Second Char
                + 
                CHAR(CASE RIGHT(@InputCode,1) WHEN '9' THEN 64 
                                         WHEN 'Z' THEN 47 
                                         ELSE ASCII(RIGHT(@InputCode,1)) 
                        END + 1) ---Third Char

RETURN @NewCode
END
GO
赞(0）分享  回复(0）举报  2022-10-31
kgqe7b3p

kgqe7b3p2#

我将其作为函数的基础，将整数转换为base26字符串

DECLARE @Input integer  = 3000

DECLARE @Value     integer  
DECLARE @Quotient  integer      = 0
DECLARE @Remainder integer      = 0
DECLARE @Output    varchar(max) = ''

DECLARE @BASE char(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'

SET @Value = @Input

WHILE @Value > 0 BEGIN

  SET @Quotient  = @Value / 26
  SET @Remainder = @Value % 26

  SET @Output = substring(@BASE,@Remainder,1) + @Output

  SELECT @Value = @Quotient

END

SELECT @Output --- DKJ
赞(0）分享  回复(0）举报  2022-10-31
ui7jx7zq

ui7jx7zq3#

试试看：

SELECT
   Letters = Char(64 + T.Num),
   T.Col1,
   T.Col2
FROM
   dbo.YourTable T
;

只是要知道，当你到了27（过去的Z），事情会变得有趣，而不是有用的。
如果你想开始把字母加倍，比如... X, Y, Z, AA, AB, AC, AD ...，那么它会变得有点棘手。这在所有版本的SQL Server中都有效。SELECT子句只是CASE语句的替代语句（每个子句短2个字符）。

SELECT
   *,
   LetterCode =
      Coalesce((SELECT Char(65 + (N.Num - 475255) / 456976 % 26) WHERE N.Num >= 475255), '')
      + Coalesce((SELECT Char(65 + (N.Num - 18279) / 17576 % 26) WHERE N.Num >= 18279), '')
      + Coalesce((SELECT Char(65 + (N.Num - 703) / 676 % 26) WHERE N.Num >= 703), '')
      + Coalesce((SELECT Char(65 + (N.Num - 27) / 26 % 26) WHERE N.Num >= 27), '')
      + (SELECT Char(65 + (N.Num - 1) % 26))
FROM dbo.YourTable N
ORDER BY N.Num
;

在SQL Fiddle观看现场演示

(Demo对于SQL 2008及更高版本，请注意，我使用Dense_Rank()来模拟一系列数字）
这将从A到ZZZZZ工作，表示值1到12356630。上面所有的疯狂而不是一个更简单的表达式的原因是因为A并不简单地表示0。在每个阈值之前，当序列跳到下一个添加到前面的字母A时，实际上有一个隐藏的，空格，数字--但是它不再被使用了。所以5个字母长度不是26^5个组合，而是26 + 26^2 + 26^3 + 26^4 + 26^5！
我花了一些真实的的修修补补才让这段代码正常工作...我希望你或其他人能欣赏它！这可以很容易地扩展到更多的字母，只需添加另一个字母生成表达式与正确的值。
既然我现在正处于一场证明男人气概的比赛中，我做了一些性能测试。对我来说，WHILE循环不是比较性能的好方法，因为我的查询被设计为一次对整个行集运行。对我来说，对一行运行一百万次它没有意义（基本上是将其强制到虚拟UDF区域），因为它可以对一百万行运行一次，这是OP给出的对大型行集执行此操作的用例。000行（测试脚本需要SQL Server 2005及更高版本）。

DECLARE
   @Buffer varchar(16),
   @Start datetime;

SET @Start = GetDate();
WITH A (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) A (N)),
B (N) AS (SELECT 1 FROM A, A X),
C (N) AS (SELECT 1 FROM B, B X),
D (N) AS (SELECT 1 FROM C, B X),
N (Num) AS (SELECT Row_Number() OVER (ORDER BY (SELECT 1)) FROM D)
SELECT @Buffer = dbo.HinkyBase26(N.Num)
FROM N
;
SELECT [HABO Elapsed Milliseconds] = DateDiff( ms, @Start, GetDate());

SET @Start = GetDate();
WITH A (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) A (N)),
B (N) AS (SELECT 1 FROM A, A X),
C (N) AS (SELECT 1 FROM B, B X),
D (N) AS (SELECT 1 FROM C, B X),
N (Num) AS (SELECT Row_Number() OVER (ORDER BY (SELECT 1)) FROM D)
SELECT
   @Buffer =
      Coalesce((SELECT Char(65 + (N.Num - 475255) / 456976 % 26) WHERE N.Num >= 475255), '')
      + Coalesce((SELECT Char(65 + (N.Num - 18279) / 17576 % 26) WHERE N.Num >= 18279), '')
      + Coalesce((SELECT Char(65 + (N.Num - 703) / 676 % 26) WHERE N.Num >= 703), '')
      + Coalesce((SELECT Char(65 + (N.Num - 27) / 26 % 26) WHERE N.Num >= 27), '')
      + (SELECT Char(65 + (N.Num - 1) % 26))   
FROM N
;
SELECT [ErikE Elapsed Milliseconds] = DateDiff( ms, @Start, GetDate());

而结果：

UDF: 17093 ms
ErikE: 12056 ms

原始查询

我最初是以一种“有趣”的方式来实现的，即为每个字母生成一行，然后使用XML进行数据透视连接，但尽管这确实很有趣，但它被证明是缓慢的。下面是为后代提供的版本（Dense_Rank需要SQL 2005及更高版本，但在SQL 2000中仅将数字转换为字母时也可以工作）：

WITH Ranks AS (
   SELECT
      Num = Dense_Rank() OVER (ORDER BY T.Sequence),
      T.Col1,
      T.Col2
   FROM
      dbo.YourTable T
)
SELECT
   *,
   LetterCode =
      (
         SELECT Char(65 + (R.Num - X.Low) / X.Div % 26)
         FROM
            (
               SELECT 18279, 475254, 17576
               UNION ALL SELECT 703, 18278, 676
               UNION ALL SELECT 27, 702, 26
               UNION ALL SELECT 1, 26, 1
            ) X (Low, High, Div)      
         WHERE R.Num >= X.Low
         FOR XML PATH(''), TYPE
      ).value('.[1]', 'varchar(4)')
FROM Ranks R
ORDER BY R.Num
;

在SQL Fiddle观看现场演示

赞(0）分享  回复(0）举报  2022-10-31
2skhul33

2skhul334#

提示：在SQL企业管理器中尝试此操作

select char(65), char(66), char(67)

对于高达17，500（或三个字母，高达ZZZ）的秩，完整的解决方案是：

select 
    case When rnk < 703 Then ''
 else Char(64 + ((rnk-26) / 26 / 26)) End +
    case When rnk < 27 Then '' 
   When rnk < 703 Then Char(64 + ((rnk-1)/ 26))
 else Char(65 + ((rnk-1)% 702 / 26)) End +
    Char(65 + ((rnk - 1) % 26))  
from (select Dense_Rank() 
     OVER (ORDER BY T.Sequence) rnk
      From YourTable t) z
赞(0）分享  回复(0）举报  2022-10-31
31moq8wy

31moq8wy5#

可使用UDF将这些值转换为以26为基数的偏移：

EDIT：修正了函数。

create function dbo.HinkyBase26( @Value as BigInt ) returns VarChar(15) as
  begin
  -- Notes: 'A' = 0.  Negative numbers are not handled.
  declare @Result as VarChar(15) = '';

  if @Value = 0
    select @Result = 'A';
  else
    set @Value += 1;
  while @Value > 0
    select @Value -= 1, @Result = Char( ASCII( 'A' ) + @Value % 26 ) + @Result, @Value /= 26;
  return @Result;
  end;

示例值：

select Arabic, dbo.HinkyBase26( Arabic ) as Alpha
  from ( values ( 0 ), ( 1 ), ( 25 ), ( 26 ), ( 51 ), ( 52 ),
    ( 27 * 26 - 1 ), ( 27 * 26 ),
    ( 33685567531 ) ) as Foo( Arabic );

在ErikE的建议下，我在笔记本电脑上进行了一次快速性能测试。UDF与XML解决方案的1，000，000次迭代：

declare @Count as Int;
declare @Buffer as VarChar(16);
declare @Start as DateTime;

select @Count = 1000000, @Start = GetDate();
while @Count > 0
  select @Buffer = dbo.HinkyBase26( @Count ), @Count -= 1;
select DateDiff( ms, @Start, GetDate() ) as 'Elapsed Milliseconds'; -- 14,583    
select @Count = 1000000, @Start = GetDate();
while @Count > 0
  select @Buffer =
      (
         SELECT Char( ASCII( 'A' ) + (@Count - X.Low) / X.Div % 26)
         FROM
            (
               SELECT 18279, 475254, 17576
               UNION ALL SELECT 703, 18278, 676
               UNION ALL SELECT 27, 702, 26
               UNION ALL SELECT 1, 26, 1
            ) X (Low, High, Div)      
         WHERE @Count >= X.Low
         FOR XML PATH(''), TYPE
      ).value('.[1]', 'varchar(4)'), @Count -= 1;
select DateDiff( ms, @Start, GetDate() ) as 'Elapsed Milliseconds'; -- 47,256

UDF的速度快了3倍多一点。
走 * 另 * 条路会是这样的：

create function dbo.DehinkyBase26( @Value as VarChar(15) ) returns BigInt as
  begin
  -- Notes: 'A' = 0.  Negative numbers are not handled.
  declare @Result as BigInt = -1;

  while @Value > ''
    select @Result = ASCII( Left( @Value, 1 ) ) - ASCII( 'A' ) + ( @Result + 1 ) * 26,
      @Value = Right( @Value, Len( @Value ) - 1 );
  return @Result;
  end;

以及：

select Arabic, dbo.HinkyBase26( Arabic ) as Alpha,
  dbo.DehinkyBase26( dbo.HinkyBase26( Arabic ) ) as RoundTrip
  from ( values ( 0 ), ( 1 ), ( 25 ), ( 26 ), ( 51 ), ( 52 ),
    ( 27 * 26 - 1 ), ( 27 * 26 ),
    ( 33685567531 ) ) as Foo( Arabic );

dbfiddle。

赞(0）分享  回复(0）举报  2022-10-31
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com