SQL:是否有可能将数字(1、2、3、4...)转换为字母(A、B、C、D...) [英] SQL: Is there a possibility to convert numbers (1,2,3,4...) to letters (A,B,C,D...)

问题描述

是否有可能获得字母(如 A、B)而不是数字(1,2)，例如由于 Dense_Rank 函数调用(在 MS Sql 中)?

Is there a possibility to obtain letters (like A,B) instead of numbers (1,2) e.g. as a result of Dense_Rank function call(in MS Sql) ?

推荐答案

试试这个:

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

请注意，当您达到 27(过去 Z)时，事情会变得有趣，但没有用处.

Just be aware that when you get to 27 (past Z), things are going to get interesting, and not useful.

如果您想开始将字母加倍，例如 ... X, Y, Z, AA, AB, AC, AD ... 那么它会变得有点棘手.这适用于所有版本的 SQL Server.SELECT 子句只是 CASE 语句的替代(每个都短 2 个字符).

If you wanted to start doubling up letters, as in ... X, Y, Z, AA, AB, AC, AD ... then it's going to get a bit trickier. This works in all versions of SQL Server. The SELECT clauses are just an alternate to a CASE statement (and 2 characters shorter, each).

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 上观看现场演示

(用于 SQL 2008 及以上的演示，注意我使用 Dense_Rank() 来模拟一系列数字)

这将从 A 到 ZZZZZ 工作，表示值 1 到 12356630.上面所有疯狂而不是更简单的表达式的原因是因为 A 不简单地代表 0，这里.当序列转移到添加到前面的下一个字母 A 时，在每个阈值之前，实际上有一个隐藏的、空白的数字——但它不会再次使用.所以 5 个字母长不是 26^5 组合，而是 26 + 26^2 + 26^3 + 26^4 + 26^5！

This will work from A to ZZZZZ, representing the values 1 to 12356630. The reason for all the craziness above instead of a more simple expression is because A doesn't simply represent 0, here. Before each threshold when the sequence kicks over to the next letter A added to the front, there is in effect a hidden, blank, digit--but it's not used again. So 5 letters long is not 26^5 combinations, it's 26 + 26^2 + 26^3 + 26^4 + 26^5!

需要一些真正的修补才能使此代码正常工作...我希望您或其他人对此表示赞赏！只需添加另一个具有正确值的字母生成表达式，就可以轻松地将其扩展为更多字母.

It took some REAL tinkering to get this code working right... I hope you or someone appreciates it! This can easily be extended to more letters just by adding another letter-generating expression with the right values.

因为现在看来我正处于男子气概比赛的中间，所以我做了一些性能测试.WHILE 循环对我来说不是比较性能的好方法，因为我的查询旨在同时针对整个行集运行.当它可以针对一百万行运行一次时，针对一行运行一百万次(基本上是强制它进入虚拟 UDF 区域)对我来说没有意义，这是 OP 给出的用例场景，用于执行这是针对大行集的.所以这是针对 1,000,000 行进行测试的脚本(测试脚本需要 SQL Server 2005 及更高版本).

Since it appears I'm now square in the middle of a proof-of-manliness match, I did some performance testing. A WHILE loop is to me not a great way to compare performance because my query is designed to run against an entire set of rows at once. It doesn't make sense to me to run it a million times against one row (basically forcing it into virtual-UDF land) when it can be run once against a million rows, which is the use case scenario given by the OP for performing this against a large rowset. So here's the script to test against 1,000,000 rows (test script requires SQL Server 2005 and up).

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

原始查询

我最初通过为每个字母生成 1 行并使用 XML 进行枢轴连接来做到这一点的有趣"方式，但虽然它确实很有趣，但事实证明它很慢.这是供后代使用的版本(Dense_Rank 需要 SQL 2005 及更高版本，但在 SQL 2000 中仅用于将数字转换为字母):

I initially did this a "fun" way by generating 1 row per letter and pivot-concatenating using XML, but while it was indeed fun, it proved to be slow. Here is that version for posterity (SQL 2005 and up required for the Dense_Rank, but will work in SQL 2000 for just converting numbers to letters):

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:是否有可能将数字(1、2、3、4...)转换为字母(A、B、C、D...)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！