Urutkan string sebagai nomor di server sql

Penggunaan REVERSE, CHARINDEX, dan SUBSTRING secara bijaksana, dapat memberikan apa yang kita inginkan. Saya telah menggunakan nama kolom yang mudah-mudahan dapat menjelaskan dalam kode saya di bawah ini untuk mengilustrasikan apa yang terjadi.

Siapkan data sampel:

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

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

SELECT * FROM @Invoice

Contoh data:

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

Dan inilah kodenya. Saya punya firasat bahwa ekspresi akhir bisa disederhanakan.

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)

Keluaran:

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

Perhatikan bahwa yang sebenarnya Anda perlukan hanyalah klausa ORDER BY, sisanya hanya untuk menunjukkan pekerjaan saya, yang berbunyi seperti ini:

• Balikkan string, cari tanda hubung, ambil substring setelah tanda hubung, balikkan bagian itu: Ini adalah angka tanpa imbuhan apa pun
• Panjang (angka tanpa imbuhan apa pun) memberitahu kita berapa banyak karakter yang harus dihilangkan dari awal untuk mendapatkan imbuhan termasuk tanda hubung. Jatuhkan karakter tambahan untuk mendapatkan bagian numerik saja, dan ubah menjadi int. Untungnya kita mendapat jeda dari SQL Server karena konversi ini memberikan nol untuk string kosong.
• Terakhir, setelah mendapatkan dua bagian ini, kita sederhanakan ORDER BY (angka tanpa imbuhan apa pun) dan kemudian dengan (nilai numerik dari imbuhan). Ini adalah urutan terakhir yang kami cari.

Kode akan lebih ringkas jika SQL Server mengizinkan kita mengatakan SUBSTRING(value, start) untuk memulai string pada saat itu, tetapi ternyata tidak, jadi kita harus sering mengatakan SUBSTRING(value, start, LEN(value)).

Coba yang ini -

Kueri:

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)

Keluaran:

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

Coba ini

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

Demo SQLFiddle

Kueri di atas menggunakan dua pernyataan kasus

1. Mengurutkan bagian pertama dari Invoiceid 790109-1 (misal: 790709)
2. Mengurutkan bagian kedua dari Invoiceid setelah dipisahkan dengan '-' 790109-1 (misalnya: 1)

Untuk pemahaman mendetail, periksa SQLfiddle di bawah ini

Demo Detail SQLFiddle

ATAU gunakan '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

Memesan setiap bagian secara terpisah adalah cara paling sederhana dan dapat diandalkan, mengapa mencari pendekatan lain? Lihatlah pertanyaan sederhana ini.

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

Banyak jawaban bagus di sini, tapi menurut saya yang ini mungkin merupakan klausa urutan paling ringkas yang efektif:

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

Demo: - SQL Fiddle

Catatan, saya menambahkan versi '790709' ke pengujian saya, karena beberapa metode yang tercantum di sini tidak memperlakukan versi tanpa sufiks lebih rendah daripada versi dengan sufiks.

Jika invoiceID Anda panjangnya bervariasi, sebelum tanda '-', maka Anda memerlukan:

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

Demo dengan panjang bervariasi sebelum tanda hubung: SQL Fiddle

Versi saya:

declare @Len int
select @Len = (select max (len (invoiceid) -  charindex ( '-', invoiceid))-1 from MyTable)

select 
invoiceid ,
cast (SUBSTRING (invoiceid ,1,charindex ( '-', invoiceid )-1) as int) * POWER (10,@Len) + 
cast (right(invoiceid, len (invoiceid) -  charindex ( '-', invoiceid)  ) as int )
from MyTable

Anda dapat menerapkan ini sebagai kolom baru di tabel Anda:

ALTER TABLE MyTable ADD COLUMN invoice_numeric_id int null
GO

declare @Len int
select @Len = (select max (len (invoiceid) -  charindex ( '-', invoiceid))-1 from MyTable)


UPDATE TABLE MyTable
SET  invoice_numeric_id = cast (SUBSTRING (invoiceid ,1,charindex ( '-', invoiceid )-1) as int) * POWER (10,@Len) + 
    cast (right(invoiceid, len (invoiceid) -  charindex ( '-', invoiceid)  ) as int )

Salah satu caranya adalah dengan membagi InvoiceId menjadi beberapa bagian, lalu mengurutkannya menjadi beberapa bagian. Disini saya menggunakan tabel turunan, tapi bisa juga dengan CTE atau tabel sementara.

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)

Pada gambar di atas, InvoiceId1 dan InvoiceId2 adalah bagian komponen dari InvoiceId. luar select mencakup bagian-bagiannya, tetapi hanya untuk tujuan demonstrasi - Anda tidak perlu melakukan ini sesuai pilihan Anda.

Tabel turunan (bagian dalam select) mengambil InvoiceId serta bagian-bagian komponennya. Cara kerjanya begini:

• Jika ada tanda hubung di InvoiceId, InvoiceId1 akan berisi bagian pertama dari angka tersebut dan InvoiceId2 akan berisi bagian kedua.
• Jika tidak ada tanda hubung, InvoiceId1 akan kosong dan InvoiceId2 akan berisi seluruh nomor.

Kasus kedua di atas (tanpa tanda hubung) kurang optimal karena idealnya InvoiceId1 berisi angka dan InvoiceId2 kosong. Membuat pemilihan bagian dalam berfungsi secara optimal akan menurunkan keterbacaan pemilihan. Saya memilih pendekatan yang tidak optimal dan lebih mudah dibaca karena cukup baik untuk memungkinkan penyortiran.

Inilah sebabnya mengapa klausa ORDER BY menguji panjangnya - klausa ini perlu menangani dua kasus di atas.

Demo di SQL Fiddle

Bagilah pengurutan menjadi dua bagian:

SQL Fiddle

Pengaturan Skema 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'

Kueri 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

Hasil:

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

Mencoba:

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