2
votes

Chaîne de commande alphanumérique A1-1-1, A1-2-1, A1-10-1, A1-2-2, A1-2-3 etc.

J'ai une colonne avec des chaînes de longueur différente qui a des tirets (-) qui séparent les chaînes alphanumériques. La chaîne pourrait ressembler à "A1-2-3". Je dois commander d'abord par "A1" puis "2" puis "3"

Je veux obtenir l'ordre suivant pour la colonne:

select barcode from tabelWithBarcodes
order by
case when len(barcode) - len(replace(barcode,'-','')) = 2 then
        len(SUBSTRING(barcode,1,charindex('-',barcode)-1))
    end
 , case when len(barcode) - len(replace(barcode,'-','')) = 2 then
     SUBSTRING(barcode,1,(charindex('-',barcode)-1))
 end


,  case when len(barcode) - len(replace(barcode,'-','')) = 2 then
        len(substring(barcode, charindex('-',barcode) + 1, charindex('-',reverse(barcode))-1))
    end
, case when len(barcode) - len(replace(barcode,'-','')) = 2 then
        substring(barcode, charindex('-',barcode) + 1, charindex('-',reverse(barcode))-1)
    end


, case when len(barcode) - len(replace(barcode,'-','')) = 2 then
    len(right(barcode,charindex('-',reverse(barcode))-1))
end
, case when len(barcode) - len(replace(barcode,'-','')) = 2 then
    right(barcode,charindex('-',reverse(barcode))-1)
end

Je peux séparer les string avec le code suivant:

declare @string varchar(max) = 'A1-2-3'
declare @first varchar(max) = SUBSTRING(@string,1,charindex('-',@string)-1)
declare @second varchar(max) = substring(@string, charindex('-',@string) + 1, charindex('-',reverse(@string))-1)
declare @third varchar(max) = right(@string,charindex('-',reverse(@string))-1)

select @first, @second, @third

Avec la logique ci-dessus, j'ai pensé que je pourrais utiliser ce qui suit: Notez que cela ne concerne que les chaînes avec 2 tirets

A1
A1-1-1
A1-1-2
A1-1-3
A1-2-1
A1-2-2
A1-2-3
A1-7
A2-1-1
A2-1-2
A2-1-3
A2-2-1
A2-2-2
A2-2-3
A2-10-1
A2-10-2
A2-10-3
A10-1-1
A10-1-2
A10-1-3
A10-2-1
A10-2-2
A10-2-3

Mais le tri ne fonctionne pas pour la deuxième et la troisième section de la chaîne. (Je n'ai pas ajouté le code pour vérifier si la chaîne ne contient qu'un seul tiret ou pas de tiret pour plus de simplicité)

Je ne sais pas si je suis sur le bon chemin ici. Quelqu'un est-il capable de résoudre ce problème?

sql sql-server sorting tsql

8 commentaires

Les parties 2 et 3 seront-elles toujours une valeur numérique? Le caractère le plus à gauche sera-t-il toujours un caractère alphabétique et les autres caractères numériques? C'est à dire. pourriez-vous avoir des valeurs telles que 'AB1-1-2' ou A17-A4-9 ''?

Je suggère de faire le fractionnement sur un CTE ou une sous-requête, puis de trouver la bonne expression d'ordre à l'extérieur. De cette façon, vous n'avez pas besoin de répéter tout le code d'analyse à chaque fois.

Pour répondre, cependant, pourquoi votre commande ne correspond pas à vos attentes, les nombres et les chaînes sont classés différemment. Avec les nombres 10> 2 , cependant, avec les chaînes '2'> '10' . Les chaînes sont classées dans l'ordre par leurs caractères de gauche à droite. '2'> '1' et donc '2' a une valeur "supérieure" à '10' .

Vos données ne sont pas normalisées, du moins dans le sens où chaque point de données peut véhiculer plusieurs informations de tri. Si vous avez vraiment besoin à long terme de trier de cette façon, je vous recommande de stocker chaque chiffre dans une colonne séparée.

@Larnu. Oui et je pourrais aussi obtenir 'AB1-2-E1'.

@EzLo. Avez-vous une suggestion à quoi ressemblerait ce code?

Quelle est votre version de SQL Server?

@dnoeth. Serveur SQL 2016

3 Réponses :

1
votes

Jusqu'à 3 tirets peuvent être couverts en jouant avec replace & parsename & patindex :

declare @TabelWithBarcodes table (id int primary key identity(1,1), barcode varchar(20) not null, unique (barcode));

insert into @TabelWithBarcodes (barcode) values
('2-2-3'),('A2-2-2'),('A2-2-1'),('A2-10-3'),('A2-10-2'),('A2-10-1'),('A2-1-3'),('A2-1-2'),('A2-1-1'),
('A10-2-3'),('A10-2-2'),('A10-2-10'),('A10-1-3'),('AA10-A111-2'),('A10-1-1'),
('A1-7'),('A1-2-3'),('A1-2-12'),('A1-2-1'),('A1-1-3'),('B1-1-2'),('A1-1-1'),('A1'),('A10-10-1'),('A12-10-1'), ('AB1-2-E1') ;

with cte as 
 (
   select barcode,
      replace(BarCode, '-', '.')
      + replicate('.0', 3 - (len(BarCode)-len(replace(BarCode, '-', '')))) as x
from @TabelWithBarcodes
 )
select * 
,  substring(parsename(x,4), 1, patindex('%[0-9]%',parsename(x,4))-1)
  ,cast(substring(parsename(x,4), patindex('%[0-9]%',parsename(x,4)), 10) as int)
  ,substring(parsename(x,3), 1, patindex('%[0-9]%',parsename(x,3))-1)
  ,cast(substring(parsename(x,3), patindex('%[0-9]%',parsename(x,3)), 10) as int)
  ,substring(parsename(x,2), 1, patindex('%[0-9]%',parsename(x,2))-1)
  ,cast(substring(parsename(x,2), patindex('%[0-9]%',parsename(x,2)), 10) as int)
  ,substring(parsename(x,1), 1, patindex('%[0-9]%',parsename(x,1))-1)
  ,cast(substring(parsename(x,1), patindex('%[0-9]%',parsename(x,1)), 10) as int)
from cte
order by
  substring(parsename(x,4), 1, patindex('%[0-9]%',parsename(x,4))-1)
  ,cast(substring(parsename(x,4), patindex('%[0-9]%',parsename(x,4)), 10) as int)
  ,substring(parsename(x,3), 1, patindex('%[0-9]%',parsename(x,3))-1)
  ,cast(substring(parsename(x,3), patindex('%[0-9]%',parsename(x,3)), 10) as int)
  ,substring(parsename(x,2), 1, patindex('%[0-9]%',parsename(x,2))-1)
  ,cast(substring(parsename(x,2), patindex('%[0-9]%',parsename(x,2)), 10) as int)
  ,substring(parsename(x,1), 1, patindex('%[0-9]%',parsename(x,1))-1)
  ,cast(substring(parsename(x,1), patindex('%[0-9]%',parsename(x,1)), 10) as int)

étendre chaque code-barres à 4 groupes en ajoutant .0 à la fin s'il manque
divisez chaque code-barres en 4 groupes
diviser chaque groupe en caractères de début et en chiffres de fin
trier d'abord par le premier caractère
puis en convertissant les chiffres en chiffres

Voir db violon

p >

4 commentaires

Faut-il trier 'B2' avant 'A10' (cette solution le sera)?

@Larnu: A10 doit venir avant B2. Mais le code ci-dessus est assez proche

@Larnu: corrigé :-)

Cela semble bon. :) Je serais intéressé à tester cela par rapport au mien pour la vitesse, mais cela signifie créer un grand ensemble de données. J'en aurai un bon. +1 cependant.

2
votes

Ce n'est pas joli, cependant ...

'A', 10, 'A', 1, 12

Ceci produit:

A1
A1-1-1
A1-1-2
A1-1-3
A1-2-1
A1-2-2
A1-2-3
A1-7
A1-A1-3
A2-1-1
A2-1-2
A2-1-3
A2-2-1
A2-2-2
A2-2-3
A2-10-1
A2-10-2
A2-10-3
A10-1-1
A10-1-2
A10-1-3
A10-2-1
A10-2-2
A10-2-3
B1-4
B2-1-2

Cela utilise 2 fonctions définies par l'utilisateur. Premièrement ou DelimitedSplit8k_Lead (j'ai utilisé DelimitedSplit8k car je n'ai pas l'autre sur mon bac à sable pour le moment). Ensuite, vous avez également NGrams8k .

J'ai vraiment devrait expliquer comment cela fonctionne, mais beurk ... (modification à venir).

OK ... (/ soupir) Ce que ça fait. Tout d'abord, nous divisons les données en ses parties pertinentes en utilisant delimitedsplit8k (_lead) . Ensuite, dans SELECT , nous utilisons FOR XML PATH pour obtenir ( uniquement ) la partie nuémique de cette chaîne (par exemple, pour 'A10' nous obtenons '10' ) et nous le convertissons en une valeur numérique (un int).

Ensuite, nous pivotons ces données dans les parties respectives. La partie alphanumérique et la partie numérique. Donc, pour la valeur 'A10-A1-12' nous nous retrouvons avec la ligne:

USE Sandbox;
GO

WITH VTE AS(
    SELECT V.SomeString
          --Randomised order
    FROM (VALUES ('A1-1-1'),
                 ('A10-1-3'),
                 ('A10-2-2'),
                 ('A1-1-3'),
                 ('A10-2-1'),
                 ('A2-2-2'),
                 ('A1-2-1'),
                 ('A1-2-2'),
                 ('A2-1-1'),
                 ('A10-1-2'),
                 ('B2-1-2'),
                 ('A1'),
                 ('A2-2-1'),
                 ('A2-10-3'),
                 ('A10-2-3'),
                 ('A2-1-2'),
                 ('B1-4'),
                 ('A2-10-2'),
                 ('A2-2-3'),
                 ('A10-1-1'),
                 ('A1-A1-3'),
                 ('A1-7'),
                 ('A2-10-1'),
                 ('A2-1-3'),
                 ('A1-1-2'),
                 ('A1-2-3')) V(SomeString)),
Splits AS(
    SELECT V.SomeString,
           DS.Item,
           DS.ItemNumber,
           CONVERT(int,STUFF((SELECT '' + NG.token
                              FROM dbo.NGrams8k(DS.item,1) NG
                              WHERE TRY_CONVERT(int, NG.Token) IS NOT NULL
                              ORDER BY NG.position
                              FOR XML PATH('')),1,0,'')) AS NumericPortion
    FROM VTE V
         CROSS APPLY dbo.DelimitedSplit8K(V.SomeString,'-') DS),
Pivoted AS(
    SELECT S.SomeString,
           MIN(CASE V.P1 WHEN S.Itemnumber THEN REPLACE(S.Item, S.NumericPortion,'') END) AS P1Alpha,
           MIN(CASE V.P1 WHEN S.Itemnumber THEN S.NumericPortion END) AS P1Numeric,
           MIN(CASE V.P2 WHEN S.Itemnumber THEN REPLACE(S.Item, S.NumericPortion,'') END) AS P2Alpha,
           MIN(CASE V.P2 WHEN S.Itemnumber THEN S.NumericPortion END) AS P2Numeric,
           MIN(CASE V.P3 WHEN S.Itemnumber THEN REPLACE(S.Item, S.NumericPortion,'') END) AS P3Alpha,
           MIN(CASE V.P3 WHEN S.Itemnumber THEN S.NumericPortion END) AS P3Numeric
    FROM Splits S
         CROSS APPLY (VALUES(1,2,3)) AS V(P1,P2,P3)
    GROUP BY S.SomeString)
SELECT P.SomeString
FROM Pivoted P
ORDER BY P.P1Alpha,
         P.P1Numeric,
         P.P2Alpha,
         P.P2Numeric,
         P.P3Alpha,
         P.P3Numeric;

Ensuite, maintenant que nous avons fait pivoter le données, nous les trions individuellement par colonne. Et voilà.

Ce sera tombera si vous avez une valeur comme 'A1A' ou '1B1 ', et honnêtement, je ne le change pas en chatter pour ça. C'était compliqué, et ce n'est vraiment pas ce que le SGBDR devrait faire.

2 commentaires

Très cool! Je vous remercie

Vous êtes le bienvenu @SCDK mais je vous suggère de prendre en compte le commentaire de Tim. Normaliser vos données est un tel ordre de tri est important.

0
votes

Une approche alternative consisterait à utiliser votre technique pour diviser la chaîne en ses 3 composants, puis à compléter à gauche ces chaînes avec des zéros non significatifs (ou des caractères de votre choix). Cela évite tout problème où la chaîne peut contenir des alphanumériques plutôt que des chiffres uniquement. Cependant, cela signifie que les chaînes contenant des caractères alphabétiques de différentes longueurs peuvent ne pas être triées comme vous pouvez vous y attendre ... Voici le code avec lequel jouer (en utilisant les définitions de L'excellente réponse de @ dnoeth ):

;with cte as 
(
   select barcode
   , case 
       when barcode like '%-%' then 
           substring(barcode,1,charindex('-',barcode)-1) 
       else 
           barcode 
       end part1
   , case 
       when barcode like '%-%' then 
           substring(barcode, charindex('-',barcode) + 1, case 
               when barcode like '%-%-%' then 
                   (charindex('-',barcode,charindex('-',barcode) + 1)) - 1 
               else 
                   len(barcode) 
               end 
           - charindex('-',barcode))
       else 
           '' 
       end part2
   , case 
       when barcode like '%-%-%' then 
           right(barcode,charindex('-',reverse(barcode))-1) --note: assumes you don't have %-%-%-%
       else 
           '' 
       end part3 
   from @TabelWithBarcodes
)
select barcode
, part1, part2, part3
, right('0000000000' + coalesce(part1,''), 10) lpad1
, right('0000000000' + coalesce(part2,''), 10) lpad2
, right('0000000000' + coalesce(part3,''), 10) lpad3
from cte
order by lpad1, lpad2, lpad3

Exemple DBFiddle

2 commentaires

Cela souffre du même problème que dnoeth. 'B2' < 'A10' . Ce devrait être l'inverse. DB <> violon .

Ah bon point @Larnu, merci ... C'est parfois comme ça que j'aurais aimé qu'il y ait un bon support regex en SQL (sans avoir à se plonger dans des fonctions CLR sur mesure).