une - sql syntax charindex




Trier la chaîne en tant que numéro dans le serveur SQL (6)

Essayer:

select invoiceid  ... order by Convert(decimal(18, 2), REPLACE(invoiceid, '-', '.'))

https://code.i-harness.com

J'ai une colonne qui contient des données comme celle-ci. les tirets indiquent plusieurs copies de la même facture et celles-ci doivent être triées par ordre croissant

790711
790109-1
790109-11
790109-2

je dois trier en ordre croissant par ce nombre mais comme il s'agit d'un champ varchar, il trie dans l'ordre alphabétique comme ceci

790109-1
790109-11
790109-2
790711

afin de résoudre ce problème, j'ai essayé de remplacer le - (tiret) avec vide, puis en le moulant comme un nombre, puis en triant sur ce

select cast(replace(invoiceid,'-','') as decimal) as invoiceSort...............order by invoiceSort asc

alors que c'est mieux et trie comme ça

            invoiceSort
790711      (790711)   <-----this is wrong now as it should come later than 790109
790109-1    (7901091)
790109-2    (7901092)
790109-11   (79010911)

Quelqu'un m'a suggéré de diviser l'identifiant de facture sur le - (tiret) et de commander par sur les 2 parties divisées

comme =====> order by split1 asc,split2 asc (790109,1)

ce qui fonctionnerait je pense mais comment je diviserais la colonne.

Les différentes fonctions de split sur internet sont celles qui retournent une table alors que dans ce cas je demanderais une fonction scalaire.

Y a-t-il d'autres approches qui peuvent être utilisées? Les données sont montrées dans la vue de grille et la vue de grille ne soutient pas le tri sur 2 colonnes par défaut (je peux l'implémenter si :)) ainsi si des approches plus simples sont là je serais très gentil.

EDIT : merci pour toutes les réponses. Alors que chaque réponse est correcte, j'ai choisi la réponse qui m'a permis d'incorporer ces colonnes dans le tri GridView avec un minimum de re-factorisation des requêtes sql.


L'utilisation CHARINDEX de REVERSE , CHARINDEX et SUBSTRING peut nous SUBSTRING ce que nous voulons. J'ai utilisé les noms des colonnes explicatives dans mon code ci-dessous pour illustrer ce qui se passe.

Configurer des exemples de données:

DECLARE @Invoice TABLE (
    InvoiceNumber nvarchar(10)
);

INSERT @Invoice VALUES
('790711')
,('790709-1')
,('790709-11')
,('790709-21')
,('790709-212')
,('790709-2')

SELECT * FROM @Invoice

Exemple de données:

InvoiceNumber
-------------
790711
790709-1
790709-11
790709-21
790709-212
790709-2

Et voici le code. J'ai un sentiment lancinant que les expressions finales pourraient être simplifiées.

SELECT 
    InvoiceNumber
    ,REVERSE(InvoiceNumber) 
        AS Reversed
    ,CHARINDEX('-',REVERSE(InvoiceNumber)) 
        AS HyphenIndexWithinReversed
    ,SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber)) 
        AS ReversedWithoutAffix
    ,SUBSTRING(InvoiceNumber,1+LEN(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))),LEN(InvoiceNumber)) 
        AS AffixIncludingHyphen
    ,SUBSTRING(InvoiceNumber,2+LEN(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))),LEN(InvoiceNumber)) 
        AS AffixExcludingHyphen
    ,CAST(
        SUBSTRING(InvoiceNumber,2+LEN(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))),LEN(InvoiceNumber))
        AS int)  
        AS AffixAsInt
    ,REVERSE(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))) 
        AS WithoutAffix
FROM @Invoice
ORDER BY
    -- WithoutAffix
    REVERSE(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))) 
    -- AffixAsInt
    ,CAST(
        SUBSTRING(InvoiceNumber,2+LEN(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))),LEN(InvoiceNumber))
        AS int)

Sortie:

InvoiceNumber Reversed   HyphenIndexWithinReversed ReversedWithoutAffix AffixIncludingHyphen AffixExcludingHyphen AffixAsInt  WithoutAffix
------------- ---------- ------------------------- -------------------- -------------------- -------------------- ----------- ------------
790709-1      1-907097   2                         907097               -1                   1                    1           790709
790709-2      2-907097   2                         907097               -2                   2                    2           790709
790709-11     11-907097  3                         907097               -11                  11                   11          790709
790709-21     12-907097  3                         907097               -21                  21                   21          790709
790709-212    212-907097 4                         907097               -212                 212                  212         790709
790711        117097     0                         117097                                                         0           790711

Notez que tout ce dont vous avez réellement besoin est la clause ORDER BY , le reste est juste pour montrer mon travail, qui va comme ceci:

  • Inverser la chaîne, trouver le trait d'union, obtenir la sous-chaîne après le trait d'union, inverser cette partie: Ceci est le numéro sans aucun affixe
  • La longueur de (le nombre sans aucun affixe) nous indique le nombre de caractères à supprimer depuis le début afin d'obtenir l'affixe incluant le trait d'union. Déposez un caractère supplémentaire pour obtenir uniquement la partie numérique et convertissez-le en int . Heureusement, nous obtenons une pause de SQL Server en ce que cette conversion donne zéro pour une chaîne vide.
  • Enfin, ayant obtenu ces deux pièces, nous avons simplement ORDER BY (le numéro sans aucun affixe) et ensuite par (la valeur numérique de l'affixe). C'est l'ordre final que nous recherchons.

Le code serait plus concis si SQL Server nous permettait de dire SUBSTRING(value, start) pour obtenir la chaîne commençant à ce point, mais ce n'est pas le cas, donc nous devons dire SUBSTRING(value, start, LEN(value)) beaucoup.


Une façon est de diviser InvoiceId en ses parties, puis de trier les parties. Ici, j'utilise une table dérivée, mais cela peut aussi être fait avec un CTE ou une table temporaire.

select InvoiceId, InvoiceId1, InvoiceId2
from
(
    select
    InvoiceId,
    substring(InvoiceId, 0, charindex('-', InvoiceId, 0)) as InvoiceId1,
    substring(InvoiceId, charindex('-', InvoiceId, 0)+1, len(InvoiceId)) as InvoiceId2
    FROM Invoice
) tmp
order by
cast((case when len(InvoiceId1) > 0 then InvoiceId1 else InvoiceId2 end) as int),
cast((case when len(InvoiceId1) > 0 then InvoiceId2 else '0' end) as int)

Dans ce qui précède, InvoiceId1 et InvoiceId2 sont les composants de InvoiceId . La select externe comprend les parties, mais uniquement à des fins de démonstration - vous n'avez pas besoin de le faire dans votre sélection.

La table dérivée (la select interne) saisit InvoiceId ainsi que les composants. La façon dont cela fonctionne est la suivante:

  • Quand il y a un tiret dans InvoiceId , InvoiceId1 contiendra la première partie du numéro et InvoiceId2 contiendra la seconde.
  • Lorsqu'il n'y a pas de tiret, InvoiceId1 sera vide et InvoiceId2 contiendra le numéro entier.

Le deuxième cas ci-dessus (pas de tiret) n'est pas optimal car, idéalement, InvoiceId1 contiendrait le numéro et InvoiceId2 serait vide. Faire fonctionner la sélection interne de façon optimale diminuerait la lisibilité du select. J'ai choisi l'approche non optimale, plus lisible, car elle est assez bonne pour permettre le tri.

C'est pourquoi la clause ORDER BY teste la longueur - elle doit gérer les deux cas ci-dessus.

Démo à SQL Fiddle


Essaye celui-là -

Question:

DECLARE @Invoice TABLE (InvoiceNumber VARCHAR(10))
INSERT @Invoice 
VALUES
      ('790711')
    , ('790709-1')
    , ('790709-21')
    , ('790709-11')
    , ('790709-211')
    , ('790709-2')

;WITH cte AS 
(
    SELECT 
          InvoiceNumber
        , lenght = LEN(InvoiceNumber)
        , delimeter = CHARINDEX('-', InvoiceNumber)
    FROM @Invoice
)
SELECT InvoiceNumber
FROM cte
CROSS JOIN (
    SELECT repl = MAX(lenght - delimeter)
    FROM cte
    WHERE delimeter != 0
) mx
ORDER BY 
      SUBSTRING(InvoiceNumber, 1, ISNULL(NULLIF(delimeter - 1, -1), lenght))
    , RIGHT(REPLICATE('0', repl) + SUBSTRING(InvoiceNumber, delimeter + 1, lenght), repl)

Sortie:

InvoiceNumber
-------------
790709-1
790709-2
790709-11
790709-21
790709-211
790711

La commande par chaque partie séparément est la manière la plus simple et la plus fiable, pourquoi chercher d'autres approches? Jetez un oeil à cette requête simple.

select *
from Invoice
order by Convert(int, SUBSTRING(invoiceid, 0, CHARINDEX('-',invoiceid+'-'))) asc,
         Convert(int, SUBSTRING(invoiceid, CHARINDEX('-',invoiceid)+1, LEN(invoiceid)-CHARINDEX('-',invoiceid))) asc

Divisez le tri en deux parties:

SQL Fiddle

Configuration du schéma MS SQL Server 2008 :

CREATE TABLE TestData
(
  data varchar(20)
)

INSERT TestData
SELECT '790711' as data
UNION
    SELECT '790109-1'
UNION
    SELECT '790109-11'
UNION 
    SELECT '790109-2'

Requête 1 :

SELECT *
FROM TestData
ORDER BY 
    FLOOR(CAST(REPLACE(data, '-', '.') AS FLOAT)),
    CASE WHEN CHARINDEX('-', data) > 0 
        THEN CAST(RIGHT(data, len(data) - CHARINDEX('-', data)) AS INT)
        ELSE 0 
    END

Résultats :

|      DATA |
-------------
|  790109-1 |
|  790109-2 |
| 790109-11 |
|    790711 |




natural-sort