SQL: Y at-il une possibilité de convertir des nombres (1,2,3,4 ...) en lettres (A, B, C, D ...)

Est-il une possibilité d'obtenir des lettres (A,B) au lieu de numéros (1,2) comme le résultat d'une de Dense_Rank appel de fonction(en MS Sql) ?

source d'informationauteur Darqer

sql sql-server

astuce: essayez ceci dans votre SQL Enterprise manager

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

une solution complète pour les rangs jusqu'à 17 500 (ou trois lettres, jusqu'à ZZZ) est:

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

Vous pouvez convertir les valeurs de décalage de la base-de 26 ans avec un UDF:

MODIFIER: correction de la fonction.

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;

Valeurs de l'échantillon:

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 de la suggestion, j'ai couru un rapide test de performance sur mon portable. 1 000 000 d'itérations de l'UDF contre le XML solution:

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

L'UDF était un peu plus de 3 fois plus rapide.

Pas une réponse directe, mais si quelqu'un a un alphabet de conversion exigence avec 3 personnages que l'on suit est ce que je fais.

/*                    
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

Une autre approche - get next code de caractère (c'est alpha-numérique). Si vous passer '00' il sera de retour '00A'

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

J'ai utilisé cela comme une base pour ma fonction pour convertir des entiers de base26 chaînes de caractères

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

Vous devez vous connecter pour publier un commentaire.