จัดเรียงสตริงเป็นตัวเลขในเซิร์ฟเวอร์ sql

การใช้ REVERSE, CHARINDEX และ SUBSTRING อย่างเหมาะสมจะทำให้เราได้สิ่งที่เราต้องการ ฉันได้ใช้ชื่อคอลัมน์ที่หวังว่าจะอธิบายได้ในโค้ดด้านล่างเพื่อแสดงให้เห็นว่าเกิดอะไรขึ้น

ตั้งค่าข้อมูลตัวอย่าง:

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

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

SELECT * FROM @Invoice

ข้อมูลตัวอย่าง:

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

และนี่คือรหัส ฉันรู้สึกจู้จี้จุกจิกที่สำนวนสุดท้ายสามารถทำให้ง่ายขึ้นได้

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)

เอาท์พุท:

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

โปรดทราบว่าสิ่งที่คุณต้องการจริงๆ คือส่วนคำสั่ง ORDER BY ที่เหลือเพียงเพื่อแสดงการทำงานของฉัน ซึ่งมีลักษณะดังนี้:

• ย้อนกลับสตริง, ค้นหายัติภังค์, รับสตริงย่อยหลังยัติภังค์, ย้อนกลับส่วนนั้น: นี่คือตัวเลขโดยไม่ต้องต่อท้ายใดๆ
• ความยาวของ (ตัวเลขที่ไม่มีส่วนต่อท้าย) บอกเราว่าต้องทิ้งอักขระจำนวนเท่าใดตั้งแต่เริ่มต้น เพื่อให้ได้ส่วนต่อท้ายรวมทั้งเครื่องหมายยัติภังค์ด้วย วางอักขระเพิ่มเติมเพื่อให้ได้เฉพาะส่วนที่เป็นตัวเลข และแปลงเป็น int โชคดีที่เราได้รับการหยุดพักจาก SQL Server เนื่องจากการแปลงนี้ให้ค่าศูนย์สำหรับสตริงว่าง
• ในที่สุด เมื่อได้สองส่วนนี้มา เราก็สร้าง ORDER BY (ตัวเลขที่ไม่มีส่วนต่อท้าย) แล้วตามด้วย (ค่าตัวเลขของส่วนต่อท้าย) นี่คือคำสั่งสุดท้ายที่เราแสวงหา

โค้ดจะกระชับกว่านี้ถ้า SQL Server อนุญาตให้เราพูด SUBSTRING(value, start) เพื่อให้สตริงเริ่มต้นที่จุดนั้น แต่ก็ไม่เป็นเช่นนั้น ดังนั้นเราจึงต้องพูด SUBSTRING(value, start, LEN(value)) บ่อยครั้ง

ลองอันนี้ -

ข้อความค้นหา:

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)

เอาต์พุต:

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

ลองสิ่งนี้

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

แบบสอบถามข้างต้นใช้คำสั่งสองกรณี

1. เรียงลำดับส่วนแรกของ Invoiceid 790109-1 (เช่น 790709)
2. เรียงลำดับส่วนที่สองของ Invoiceid หลังจากแยกด้วย '-' 790109-1 (เช่น: 1)

เพื่อความเข้าใจโดยละเอียดให้ตรวจสอบ SQLfiddle ด้านล่าง

การสาธิตรายละเอียด SQLFiddle

หรือใช้ '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

การสั่งซื้อแยกชิ้นส่วนเป็นวิธีที่ง่ายที่สุดและเชื่อถือได้ เหตุใดจึงมองหาแนวทางอื่น ลองดูคำถามง่ายๆ นี้

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

มีคำตอบที่ดีมากมายที่นี่ แต่ฉันคิดว่าคำตอบนี้อาจเป็นลำดับที่กะทัดรัดที่สุดตามข้อที่มีประสิทธิภาพ:

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

การสาธิต: - SQL Fiddle

โปรดทราบว่าฉันได้เพิ่มเวอร์ชัน '790709' ลงในการทดสอบ เนื่องจากวิธีการบางอย่างที่แสดงไว้ที่นี่ไม่ได้ถือว่าเวอร์ชันที่ไม่มีส่วนต่อท้ายน้อยกว่าเวอร์ชันที่มีส่วนต่อท้าย

หาก ID ใบแจ้งหนี้ของคุณมีความยาวต่างกัน นำหน้า '-' คุณจะต้อง:

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

การสาธิตที่มีความยาวต่างกันก่อนเครื่องหมายขีด: SQL Fiddle

เวอร์ชันของฉัน:

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

คุณสามารถใช้สิ่งนี้เป็นคอลัมน์ใหม่ในตารางของคุณ:

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 )

วิธีหนึ่งคือแยก InvoiceId ออกเป็นส่วนๆ แล้วเรียงลำดับส่วนต่างๆ ที่นี่ฉันใช้ตารางที่ได้รับ แต่สามารถทำได้ด้วย CTE หรือตารางชั่วคราวเช่นกัน

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)

จากข้างต้น InvoiceId1 และ InvoiceId2 เป็นส่วนประกอบของ InvoiceId ด้านนอก select มีชิ้นส่วนต่างๆ เพื่อการสาธิตเท่านั้น คุณไม่จำเป็นต้องดำเนินการนี้ในการเลือก

ตารางที่ได้รับ (ด้านใน select) คว้า InvoiceId เช่นเดียวกับส่วนประกอบต่างๆ วิธีการทำงานคือ:

• เมื่อมีขีดกลางใน InvoiceId นั้น InvoiceId1 จะมีส่วนแรกของตัวเลข และ InvoiceId2 จะมีส่วนที่สอง
• เมื่อไม่มีขีดกลาง InvoiceId1 จะว่างเปล่า และ InvoiceId2 จะมีตัวเลขทั้งหมด

กรณีที่สองด้านบน (ไม่มีขีดกลาง) ไม่เหมาะสม เนื่องจากตามหลักการแล้ว InvoiceId1 จะมีตัวเลข และ InvoiceId2 จะว่างเปล่า เพื่อให้การเลือกภายในทำงานได้อย่างเหมาะสมจะลดความสามารถในการอ่านของการเลือก ฉันเลือกแนวทางที่ไม่เหมาะสมและอ่านง่ายกว่า เนื่องจากดีพอที่จะจัดเรียงได้

นี่คือสาเหตุที่คำสั่งย่อย ORDER BY ทดสอบความยาว - จำเป็นต้องจัดการกับสองกรณีข้างต้น

การสาธิตที่ SQL Fiddle

แบ่งการเรียงลำดับออกเป็นสองส่วน:

ซอ SQL

การตั้งค่า Schema 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'

ข้อความค้นหา 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

ผลลัพธ์:

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

พยายาม:

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