SQL提取字段冒号前首值并按其倒序排序实现咨询

Hey there! I see you're just getting started with SQL and ran into a tricky sorting problem—let me walk you through how to fix it.

The core of your issue is extracting the first part of the string (before the colon :) from your testColumn and using that value to sort your results in descending order. The exact method depends a bit on which SQL database you're using, so I'll cover the most common ones below:

MySQL/MariaDB #

Use the SUBSTRING_INDEX() function, which is perfect for splitting strings by a delimiter and grabbing a specific part. We'll also cast the extracted value to a number to make sure sorting works correctly (since string sorting would treat "10" as smaller than "2"):

SELECT * 
FROM test 
ORDER BY CAST(SUBSTRING_INDEX(testColumn, ':', 1) AS UNSIGNED) DESC;

PostgreSQL #

PostgreSQL has a handy SPLIT_PART() function that does the same job. Again, casting to an integer ensures proper numeric sorting:

SELECT * 
FROM test 
ORDER BY CAST(SPLIT_PART(testColumn, ':', 1) AS INTEGER) DESC;

SQL Server #

For SQL Server, we'll combine CHARINDEX() to find the position of the colon and SUBSTRING() to extract the part before it:

SELECT * 
FROM test 
ORDER BY CAST(SUBSTRING(testColumn, 1, CHARINDEX(':', testColumn) - 1) AS INT) DESC;

Quick note for edge cases #

If some rows in testColumn don't have a colon at all, you'll want to add a fallback to avoid errors. Here's how you'd adjust the MySQL example to handle that:

SELECT * 
FROM test 
ORDER BY CAST(
    CASE 
        WHEN CHARINDEX(':', testColumn) > 0 THEN SUBSTRING_INDEX(testColumn, ':', 1)
        ELSE testColumn
    END AS UNSIGNED
) DESC;

Let me know if you're using a different database or run into any issues—I'm happy to help tweak this further!

备注：内容来源于stack exchange，提问作者user22717183