SQL: есть ли возможность конвертировать числа (1,2,3,4...) в письма (A, B, C, D...)

Ответ 1

Попробуйте следующее:

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

(Демо для 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 для выполнения это против большого набора строк. Итак, здесь script для тестирования против 1 000 000 строк (для теста script требуется 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

Исходный запрос

Я изначально сделал это "забавным" способом, создав 1 строку на каждую букву и сводную конкатенацию с использованием XML, но, хотя это было действительно весело, это оказалось медленным. Вот эта версия для потомков (SQL 2005 и выше требуется для Dense_Rank, но будет работать в 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

Ответ 2

: попробуйте это в своем менеджере SQL Enterprise

  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

Ответ 3

Вы можете преобразовать значения в базу смещения-26 с помощью UDF:

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 я провел быстрый тест производительности на своем ноутбуке. 1,000,000 итераций UDF по сравнению с XML-решением:

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 раза быстрее.

Ответ 4

Не прямой ответ - но если у кого-то есть требование преобразования алфавита с 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

Другой подход - получить следующий код символа (это альфа-числовой результат). Если вы пройдете '00A', он вернет '00B'

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