SQL Server中ORDER BY ASC排序：含字母数字的升序实现方法

Got it, let's work through your sorting requirements for both data sets. The key here is that plain string-based sorting won't give you the numeric order you need—we have to extract the numeric suffix (or treat the base value as a "0" suffix) and sort by that numeric value instead.

For the first data set (numeric base values with space-separated suffixes) #

Your data looks like 463919493, 463919493 01, etc. To sort these by the numeric suffix in ascending order (with the base value first), use a CASE statement to extract and convert the suffix to an integer. Here's the query:

SELECT YourColumnName
FROM YourTableName
WHERE YourColumnName LIKE '463919493%' -- Filter to target this specific data set
ORDER BY
    CASE
        -- Check if a space exists to separate the base value and suffix
        WHEN CHARINDEX(' ', YourColumnName) > 0 THEN
            -- Extract the suffix part and convert it to an integer for numeric sorting
            CAST(RIGHT(YourColumnName, LEN(YourColumnName) - CHARINDEX(' ', YourColumnName)) AS INT)
        ELSE
            0 -- Treat the base value (no suffix) as having a "0" suffix to place it first
    END ASC;

How this works: #

• CHARINDEX(' ', YourColumnName) locates the space in the string. If present, we pull everything after that space.
• Converting the suffix to an integer ensures we sort by numeric value (so 01 comes before 02, which is what you want) instead of lexicographical string order.
• The base value (no suffix) gets assigned a value of 0, so it will appear first in the sorted results.

For the second data set (alphanumeric prefix with numeric base and suffix) #

Your data here has a letter prefix like HO463919493, HO463919493 01. The logic is nearly identical—we still focus on the numeric suffix, regardless of the leading letters. Here's the query:

SELECT YourColumnName
FROM YourTableName
WHERE YourColumnName LIKE 'HO463919493%' -- Filter to target this specific data set
ORDER BY
    CASE
        WHEN CHARINDEX(' ', YourColumnName) > 0 THEN
            CAST(RIGHT(YourColumnName, LEN(YourColumnName) - CHARINDEX(' ', YourColumnName)) AS INT)
        ELSE
            0
    END ASC;

If your prefixes vary (but follow the same structure) #

If you have different prefixes (e.g., AB123, XY123 01) and need to sort first by the base numeric value, then the suffix, adjust the query to extract the base number too:

SELECT YourColumnName
FROM YourTableName
ORDER BY
    -- Extract the base numeric part (everything after the prefix, before the space)
    CAST(
        SUBSTRING(
            YourColumnName,
            PATINDEX('%[0-9]%', YourColumnName),
            CASE WHEN CHARINDEX(' ', YourColumnName) > 0 THEN CHARINDEX(' ', YourColumnName) - PATINDEX('%[0-9]%', YourColumnName) ELSE LEN(YourColumnName) - PATINDEX('%[0-9]%', YourColumnName) + 1 END
        ) AS INT
    ) ASC,
    -- Then sort by the suffix
    CASE
        WHEN CHARINDEX(' ', YourColumnName) > 0 THEN CAST(RIGHT(YourColumnName, LEN(YourColumnName) - CHARINDEX(' ', YourColumnName)) AS INT)
        ELSE 0
    END ASC;

This handles cases where the base numeric values differ across rows, sorting first by the base number, then the suffix.

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