Art string als Zahl in sql server

Vernünftige Nutzung von REVERSE, CHARINDEX, und SUBSTRING Holen uns was wir wollen. Benutzt habe ich hoffentlich selbsterklärenden Spalten-Namen in meinem code unten, um zu veranschaulichen, was Los ist.

Einrichten-Beispiel Daten:

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

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

SELECT * FROM @Invoice

Beispieldaten:

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

Und hier ist der code. Ich habe ein nagendes Gefühl, die letzten Ausdrücke vereinfacht werden könnte.

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)

Ausgabe:

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

Beachten Sie, dass alles, was Sie wirklich brauchen, ist die ORDER BY Klausel, der rest ist nur zu zeigen, meine Arbeit, und der geht so:

• Umkehren der Zeichenfolge ist, finden Sie den Bindestrich, bekommen die Teilzeichenfolge nach dem Bindestrich, reverse-Teil: Dieser ist die Zahl ohne affix
• Die Länge (die Zahl ohne affix) sagt aus, wie viele Zeichen zu Tropfen von Anfang an in order to get the anzubringen, einschließlich der Bindestriche. Drop ein zusätzliches Zeichen erhalten nur den numerischen Teil und wandeln diese in int. Glücklicherweise erhalten wir eine Pause von SQL Server, die diese Umwandlung gibt null für eine leere Zeichenfolge.
• Schließlich, nachdem ich diese beiden Stücke, die wir einfach ORDER BY (die Zahl ohne kleben) und dann von (der numerische Wert des zu kleben). Dies ist die Letzte Bestellung, die wir anstreben.

Der code wäre übersichtlicher, wenn SQL Server erlaubt uns zu sagen SUBSTRING(value, start) um die Zeichenfolge beginnend an diesem Punkt, aber es funktioniert nicht, so müssen wir sagen SUBSTRING(value, start, LEN(value)) viel.

Versuchen, diese

SELECT invoiceid FROM Invoice
ORDER BY 
CASE WHEN PatIndex('%[-]%',invoiceid) > 0
      THEN LEFT(invoiceid,PatIndex('%[-]%',invoiceid)-1)
      ELSE invoiceid END * 1
,CASE WHEN PatIndex('%[-]%',REVERSE(invoiceid)) > 0
      THEN RIGHT(invoiceid,PatIndex('%[-]%',REVERSE(invoiceid))-1)
      ELSE NULL END * 1

SQLFiddle Demo

Obige Abfrage verwendet zwei case-Anweisungen

1. Möglichen ersten Teil Invoiceid 790109-1 (zB: 790709)
2. Möglichen zweiten Teil Invoiceid nach splitting '-' 790109-1 (z.B.: 1)

Detaillierte Verständnis prüfen Sie die unten SQLfiddle

SQLFiddle Detaillierte Demo

Verwenden ODER 'CHARINDEX'

SELECT invoiceid FROM Invoice
ORDER BY 
CASE WHEN CHARINDEX('-', invoiceid) > 0
      THEN LEFT(invoiceid, CHARINDEX('-', invoiceid)-1)
      ELSE invoiceid END * 1
,CASE WHEN CHARINDEX('-', REVERSE(invoiceid)) > 0
      THEN RIGHT(invoiceid, CHARINDEX('-', REVERSE(invoiceid))-1)
      ELSE NULL END * 1

Viele gute Antworten hier, aber ich denke, das könnten die meisten kompakten order by-Klausel wirksam ist:

SELECT *
FROM Invoice
ORDER BY LEFT(InvoiceId,CHARINDEX('-',InvoiceId+'-'))
         ,CAST(RIGHT(InvoiceId,CHARINDEX('-',REVERSE(InvoiceId)+'-'))AS INT)DESC

Demo: - SQL Fiddle

Hinweis, ich habe das '790709' - version auf meinem test, da einige der hier aufgeführten Methoden sind nicht die Behandlung der no-suffix version kleiner als die mit-suffix-Versionen.

Wenn Ihr invoiceID in der Länge variiert werden, vor dem '-' ist, dann würden Sie brauchen:

SELECT *
FROM Invoice
ORDER BY CAST(LEFT(list,CHARINDEX('-',list+'-')-1)AS INT)
         ,CAST(RIGHT(InvoiceId,CHARINDEX('-',REVERSE(InvoiceId)+'-'))AS INT)DESC

Demo mit unterschiedlichen Längen vor dem Bindestrich: SQL Fiddle

Eine Möglichkeit ist die split InvoiceId in seine Teile, und dann Sortieren Sie die Teile. Hier habe ich eine abgeleitete Tabelle verwenden, aber es könnte getan werden, mit einem CTE oder eine temporäre Tabelle.

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)

In der oben InvoiceId1 und InvoiceId2 sind die Teile InvoiceId. Die äußeren select enthält die Teile, aber nur für Demo-Zwecke - Sie brauchen nicht, dies zu tun in Ihrem wählen.

Die abgeleitete Tabelle (die innere select) packt die InvoiceId sowie die Bauteile. Die Weise, die es funktioniert, ist dies:

• Wenn es ein Strich in InvoiceId, InvoiceId1 enthält der erste Teil der Nummer und InvoiceId2 enthält die zweite.
• Wenn es nicht einen Strich, InvoiceId1 leer und der InvoiceId2 enthält die gesamte Anzahl.

Zweiten Fall oben (ohne Bindestrich) ist nicht optimal, da im Idealfall InvoiceId1 enthalten würde, die Anzahl und die InvoiceId2 leer wäre. Um die innere select-optimal funktionieren würde, verringern die Lesbarkeit des wählen Sie. Ich entschied mich für die nicht optimale, besser lesbar Ansatz, da ist es gut genug, um für die Sortierung.

Dies ist der Grund, warum die ORDER BY Klausel tests für die Länge - es muss behandeln die beiden Fälle oben.

Demo auf SQL Fiddle

Versuchen:

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