如何对指定数据集进行字母数字排序？SQL实现方案咨询

Great question—let's break this down step by step. First, we need to tackle natural alphanumeric sorting for your dataset: that means sorting text alphabetically, and embedded numbers numerically (so Transmitter #1 comes before Transmitter #10, not the other way around).

Step 1: Understand Your Dataset's Structure #

Your items fall into a few distinct categories:

• Items with a text prefix + trailing number (e.g., Transmitter #1, Allergy Guard – 3)
• Items with a number in the middle (e.g., Room 1 Transmitter)
• Items with no embedded numbers (e.g., Deli Counter)

Step 2: Validate Your Provided SQL Statement #

Your current query has three critical issues that make it ineffective for natural sorting:

SELECT * FROM #table ORDER BY Name, CASE WHEN patindex('%[0-9]%',substring(Name,len(Name),LEN(Name))) =1 THEN cast(substring(Name,patindex('%[0-9]%', Name),len(Name)) as int) END

Key Issues Breakdown: #

1. Incorrect number check: The condition only verifies if the last character is a number. This fails for items like Room 1 Transmitter (where the number is in the middle), so the CASE statement won’t trigger, and those items will sort purely by raw string value.
2. Invalid number extraction: When you do try to extract a number (e.g., for Room 1 Transmitter), the substring returns 1 Transmitter—which can’t be cast to an integer (it contains non-numeric characters). This will throw a conversion error.
3. String sorting takes priority: Your first sort column is Name, so even if the CASE worked, string sorting would still place Transmitter #10 before Transmitter #2 (since "#10" is lex smaller than "#2").

Step 3: Corrected SQL for Natural Alphanumeric Sorting #

To fix this, we need to separate the text prefix from the embedded numeric part, then sort by the prefix first, then the numeric part as an integer. Here’s a robust query that works for all your dataset items:

SELECT *
FROM #table
ORDER BY
    -- Sort by text prefix: for items with numbers, take everything before the first digit; for others, use the full name
    CASE 
        WHEN PATINDEX('%[0-9]%', Name) > 0 THEN LEFT(Name, PATINDEX('%[0-9]%', Name) - 1)
        ELSE Name
    END,
    -- Sort by numeric part: extract only consecutive digits, cast to int; no numbers = 0 (adjust to 999999 if you want them last)
    CASE
        WHEN PATINDEX('%[0-9]%', Name) > 0 THEN CAST(
            SUBSTRING(
                Name,
                PATINDEX('%[0-9]%', Name),
                PATINDEX('%[^0-9]%', SUBSTRING(Name, PATINDEX('%[0-9]%', Name), LEN(Name)) + ' ') - 1
            ) AS INT
        )
        ELSE 0
    END

How This Works: #

• Text Prefix Extraction: LEFT(Name, PATINDEX('%[0-9]%', Name) - 1) grabs everything before the first digit. For items without digits, we use the full name.
• Numeric Part Extraction: The inner SUBSTRING starts at the first digit, then PATINDEX('%[^0-9]%', ...) finds the first non-digit after that—so we only extract consecutive digits (no trailing text), ensuring safe casting to an integer.
• Sort Order: First alphabetically by the text prefix, then numerically by the embedded number. Items without numbers will group together at the top (since we set their numeric part to 0).

Example Sorted Output #

Running this query on your dataset will produce this natural sorted order:

• Allergy Guard – 1
• Allergy Guard – 2
• Allergy Guard – 3
• Deli Counter
• Room 1 Transmitter
• Transmitter #1
• Transmitter #2
• Transmitter #3
• Transmitter #4
• Transmitter #5
• Transmitter #6
• Transmitter #7
• Transmitter #8
• Transmitter #9
• Transmitter #10
• Transmitter #11

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