SQL Server实现先字母后数字的VARCHAR排序问题求助

Let's break down why your current query isn't delivering the expected order, then fix it step by step.

What's Wrong With Your Current Query? #

Your existing CASE statement lumps all alphabetic-starting values (including S 10-12 and similar entries) into the same priority group. This means they get sorted lexicographically, which is why S ... entries end up before UITS (since 'S' comes before 'U' in string order). Additionally, numeric values are treated as strings, which explains why -9 lands at the end instead of before positive numbers.

The Solution: Layered Custom Sorting #

We need to split the values into three distinct priority groups, then sort within each group to match your desired order:

1. Pure alphabetic values: LEV, PLG, UITS (in your exact preferred order)
2. S-prefixed range values: S 10-12, S 12-14, etc. (lexicographic order works here since their format is consistent)
3. Numeric values (including negatives): Sorted numerically so -9, -10 come before positive numbers

Here's the adjusted query:

SELECT Plantmaat 
FROM Plant 
GROUP BY Plantmaat 
ORDER BY 
    -- Step 1: Assign priority levels to each value group
    CASE 
        WHEN Plantmaat IN ('LEV', 'PLG', 'UITS') THEN 1
        WHEN Plantmaat LIKE 'S %' THEN 2
        ELSE 3
    END ASC,
    -- Step 2: Enforce custom order for pure alphabetic entries
    CASE Plantmaat
        WHEN 'LEV' THEN 1
        WHEN 'PLG' THEN 2
        WHEN 'UITS' THEN 3
    END ASC,
    -- Step 3: Sort S-prefixed ranges (consistent format makes lex order work)
    Plantmaat ASC,
    -- Step 4: Sort numeric values numerically (trim whitespace to handle stray spaces)
    CASE 
        WHEN Plantmaat NOT IN ('LEV', 'PLG', 'UITS') AND NOT Plantmaat LIKE 'S %' 
        THEN CAST(TRIM(Plantmaat) AS SIGNED) 
    END ASC;

How This Works: #

• The first CASE ensures pure alphabetic values come first, followed by S-prefixed ranges, then numeric values.
• The second CASE locks in your desired order for the pure alphabetic entries (LEV → PLG → UITS).
• For numeric values, TRIM() cleans up accidental whitespace, and casting to a signed integer ensures we sort by actual numeric value instead of string order (so -9 and -10 land before positive numbers, ordered correctly).

Edge Cases to Consider: #

• If you have additional pure alphabetic values beyond LEV, PLG, UITS, adjust the IN clause or add more conditions to the first CASE to include them.
• If S-prefixed ranges have inconsistent formatting (e.g., S10-12 without a space), update the LIKE condition to LIKE 'S%'.

内容的提问来源于stack exchange，提问作者Lucdabomb