Je dois transformer un code numérique en un code alphabétique comme celui-ci:
1234 -> ABCD
où 0 = 0, 1 = A, 2 = b, etc. p>
Voici ma fonction et comment l'utiliser:
brand_code decoded_code 1234 ABCD 5834 EHCD 9905 II0E 0250 0BE0
Cette fonction fonctionne bien, mais dans la base de données de production, nous n'avons pas l'autorisation de créer des fonctions. p >
J'ai essayé de transformer cette fonction en CTE comme ceci
declare @change varchar(9) = 'ABCDEFGHI'
DECLARE @II int = 0
;WITH x AS
(
SELECT TOP (10) n = ROW_NUMBER() OVER (ORDER BY Number)
FROM master.dbo.spt_values ORDER BY Number
),
innerCTE as
(
SELECT x.n, SUBSTRING(t.brand_code, x.n, 1) chnum,
case SUBSTRING(t.brand_code, x.n, 1)
when '0' then '0'
else char(65-1+SUBSTRING(t.brand_code, x.n, 1))
end chalfa, t.brand_code
FROM x INNER JOIN (VALUES('1234'),('5834'),('9905'),('0250')) as t(brand_code)
ON x.n <= LEN(t.brand_code)
),
CTE as
(
select n, chnum, chalfa, brand_code, stuff(brand_code, n, 1, chalfa) as code
from innerCTE
union all
select n+1, chnum, chalfa, brand_code, STUFF(code, n+1, 1, chalfa) as code
from cte
where n+1 <= LEN(cte.brand_code)
)
--select * from innerCTE
select * from CTE;
ou en utilisant CROSS APPLY comme cet exemple: Example_1
ou en utilisant CROSS APPLIQUEZ avec PIVOT comme cet exemple: Example_2
mais mon expérience en SQL est faible et je n'ai pas pu obtenir le résultat correct.
J'aimerais avoir ceci: p >
Create function dbo.DecodeNumToChar
(
@change varchar(10),
@foo varchar(10)
) returns varchar(10)
as
begin
DECLARE @II int = 1,
@result varchar(10)
;WITH x AS
(
SELECT @II as ii, STUFF(@foo,@II,1,SUBSTRING(@change,CAST(SUBSTRING(@foo,@II,1) AS INT)+1,1)) AS AA
UNION ALL
--SELECT @II+1
SELECT ii+1, STUFF(AA,ii+1,1,SUBSTRING(@change,CAST(SUBSTRING(@foo,ii+1,1) AS INT)+1,1)) AS AA
FROM x
where ii+1 <= LEN(@foo)
)
select top 1 @result = AA from x order by ii desc
return @result
end
--------------------------------------------
select brand_code, dbo.DecodeNumToChar('0ABCDEFGHI', brand_code)
from (VALUES('1234'),('5834'),('9905'),('0250')) as t(brand_code)
merci
4 Réponses :
La solution suivante utilise une sous-requête FOR XML et des opérations mod (% ) sur la valeur entière pour diviser chaque chiffre, puis une table de mappage pour relier chaque chiffre avec un caractère. Le FOR XML renvoie les chiffres dans l'ordre.
Integer Conversion 1234 ABCD 6485834 FDHEHCD 99084705 II0HDG0E 1124601 AABDF0A
Résultat:
DECLARE @IntegerValues TABLE (Integer INT)
INSERT INTO @IntegerValues (Integer)
VALUES
(1234),
(6485834),
(99084705),
(1124601)
SELECT
T.Integer,
Conversion = (
SELECT
'' + M.Character -- Must have no declared alias (for xml)
FROM
(VALUES
(1, T.Integer % 10),
(2, T.Integer / 10 % 10),
(3, T.Integer / 100 % 10),
(4, T.Integer / 1000 % 10),
(5, T.Integer / 10000 % 10),
(6, T.Integer / 100000 % 10),
(7, T.Integer / 1000000 % 10),
(8, T.Integer / 10000000 % 10),
(9, T.Integer / 100000000 % 10),
(10, T.Integer / 1000000000 % 10),
(11, T.Integer / 10000000000 % 10)
) AS X(OrdinalPosition, SplitDigit)
INNER JOIN (VALUES
(0, '0'),
(1, 'A'),
(2, 'B'),
(3, 'C'),
(4, 'D'),
(5, 'E'),
(6, 'F'),
(7, 'G'),
(8, 'H'),
(9, 'I')
) AS M(Digit, Character) ON X.SplitDigit = M.Digit
WHERE
X.OrdinalPosition <= FLOOR(LOG10(T.Integer)) + 1 -- This expression returns the number of digits
ORDER BY
X.OrdinalPosition DESC
FOR XML
PATH('')
)
FROM
@IntegerValues AS T
Il y a probablement un nettoyeur façon d'écrire les mappages et les opérations de mod, mais cela devrait donner une idée.
Salut @EzLo, merci pour votre solution, ça marche. Dans le même temps, j'ai résolu avec une autre requête que vous pouvez lire ici dans ma réponse
J'ai trouvé une solution utilisant la CTE récursive.
declare @change varchar(10) = '0ABCDEFGHI'
DECLARE @II int = 1;
with BRANDS as
(
select * from (VALUES('1234'),('5834'),('9905'),('0250')) as t(brand_code)
),
CTE as
(
select @II as ii, BRAND_CODE,
STUFF(brand_code,@II,1,SUBSTRING(@change,CAST(SUBSTRING(brand_code,@II,1) AS INT)+1,1)) AS AA
from BRANDS
union all
select c.ii+1, b.BRAND_CODE,
STUFF(AA,c.ii+1,1,SUBSTRING(@change,CAST(SUBSTRING(AA,c.ii+1,1) AS INT)+1,1)) AS AA
from BRANDS b
inner join CTE c on b.BRAND_CODE = c.BRAND_CODE
where c.ii < LEN(b.BRAND_CODE)
)
select BRAND_CODE, AA as NewCode from CTE where ii = len(brand_code) order by BRAND_CODE, ii
Les fonctions de récursivité + chaîne peuvent nuire aux performances si vous devez convertir des milliers d'enregistrements en une seule fois. Essayez la solution avec une quantité décente de valeurs de test et voyez si elle répond à vos besoins. Veuillez également essayer les suggestions de Martin, c'est très simple et pourrait être la solution la plus rapide à ce jour.
@EzLo je vois ... donc .. Dans mon cas, il y a 300 enregistrements et codes avec une longueur de 3 chiffres :-)
@GlaucoCucchiar Si c'est une action ponctuelle , cela n'a pas d'importance. Si vous en avez besoin plus souvent et que vous n'avez pas plus de 3 chiffres, je penserais à une table physique de 0 à 999 avec les codes dans une deuxième colonne. Générez-le une fois et utilisez-le simplement en le joignant à votre ensemble ...
Si vous utilisez SQL 2017+
DECLARE @integerValues TABLE ([I] INT);
INSERT INTO @integerValues ([I])
VALUES
(1234),
(6485834),
(99084705),
(1124601);
SELECT
[I],
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
CAST([I] AS VARCHAR(10)),
'1',
'A'),
'2',
'B'),
'3',
'C'),
'4',
'D'),
'5',
'E'),
'6',
'F'),
'7',
'G'),
'8',
'H'),
'9',
'I') [S]
FROM
@integerValues;
ou, si 2016 ou antérieur
DECLARE @integerValues TABLE ([I] INT);
INSERT INTO @integerValues ([I])
VALUES
(1234),
(6485834),
(99084705),
(1124601);
SELECT
T.[I],
STRING_AGG(
CASE SUBSTRING(T.[S], V.[number] + 1, 1)
WHEN '9' THEN 'I'
WHEN '8' THEN 'H'
WHEN '7' THEN 'G'
WHEN '6' THEN 'F'
WHEN '5' THEN 'E'
WHEN '4' THEN 'D'
WHEN '3' THEN 'C'
WHEN '2' THEN 'B'
WHEN '1' THEN 'A'
ELSE '0'
END,
'') WITHIN GROUP (ORDER BY T.[I]) [S]
FROM
(SELECT [I], CAST([I] AS VARCHAR(10)) [S] FROM @integerValues) T
JOIN
[master]..[spt_values] V ON V.[number] < LEN(T.[S])
WHERE
V.[type] = 'P';
Voici le code de la suggestion de @Martin Smith d'utiliser 10 remplacements imbriqués.
DECLARE @change char(10) = '0ABCDEFGHI';
--------------------------------------------
select brand_code,
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( brand_code
, '0', SUBSTRING( @change, 1, 1)) , '1', SUBSTRING( @change, 2, 1))
, '2', SUBSTRING( @change, 3, 1)) , '3', SUBSTRING( @change, 4, 1))
, '4', SUBSTRING( @change, 5, 1)) , '5', SUBSTRING( @change, 6, 1))
, '6', SUBSTRING( @change, 7, 1)) , '7', SUBSTRING( @change, 8, 1))
, '8', SUBSTRING( @change, 9, 1)) , '9', SUBSTRING( @change, 10, 1))
from (VALUES('1234'),('5834'),('9905'),('0250')) as t(brand_code);
Je ne sais pas pourquoi vous utilisez SUBSTRING .
Je l'utilise juste au cas où la chaîne utilisée pour changer les valeurs serait modifiée. Si les valeurs restent les mêmes, je conviens que la valeur codée en dur suffirait et nous pouvons supprimer le SUBSTRING.
J'utiliserais simplement 10
REPLACEimbriqués en supposant que vous n'êtes pas sur une version qui a réellementDECODELa pointe de Martin est la plus simple et la plus directe.
Le moment semble opportun pour utiliser Intégration CLR
@Jodrell vous avez définitivement raison mais je ne sais pas si je peux sur l'environnement PROD