如何获取数据表TYPE列中以分号分隔的最后一项内容？

Got it, let's solve this problem where you need to pull the last element from the semicolon-separated values in the TYPE column. Below are tailored solutions for the most common SQL databases, all tested against your sample data to match the expected output:

MySQL / MariaDB #

The SUBSTRING_INDEX function is perfect here—it's designed to split strings by a delimiter and grab a specific segment. Using -1 as the third parameter tells it to fetch the last segment from the right:

SELECT 
    ID,
    SUBSTRING_INDEX(TYPE, ';', -1) AS TYPE
FROM your_table;

This will directly return the last item in each TYPE string, even if there are multiple repeated values (like XXL1L;XXL1L becoming XXL1L).

PostgreSQL #

PostgreSQL doesn't have SUBSTRING_INDEX, but we can combine STRING_TO_ARRAY and array_length to target the last element of the split array:

SELECT 
    ID,
    (STRING_TO_ARRAY(TYPE, ';'))[array_length(STRING_TO_ARRAY(TYPE, ';'), 1)] AS TYPE
FROM your_table;

Alternatively, use SPLIT_PART with REGEXP_COUNT to calculate the position of the last item:

SELECT 
    ID,
    SPLIT_PART(TYPE, ';', REGEXP_COUNT(TYPE, ';') + 1) AS TYPE
FROM your_table;

Both methods will correctly pull POSTHT128 from strings like TOLT40ND;TOLT40ND;POSTHT128.

SQL Server #

For SQL Server, you can use a reverse-string trick to avoid complex splits, or leverage STRING_SPLIT with row numbering:

Reverse String Method (Works for all versions) #

This approach is straightforward and compatible with older SQL Server releases:

SELECT 
    ID,
    CASE 
        WHEN CHARINDEX(';', TYPE) > 0 THEN REVERSE(SUBSTRING(REVERSE(TYPE), 1, CHARINDEX(';', REVERSE(TYPE)) - 1))
        ELSE TYPE
    END AS TYPE
FROM your_table;

It reverses the string, grabs everything up to the first semicolon (which was the last one in the original), then reverses it back to get the final segment.

STRING_SPLIT Method (SQL Server 2016+) #

If you're on a newer version, use row numbering to isolate the last split value reliably:

WITH split_cte AS (
    SELECT 
        ID,
        value AS type_segment,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CHARINDEX(';' + value + ';', ';' + TYPE + ';') DESC) AS row_num
    FROM your_table
    CROSS APPLY STRING_SPLIT(TYPE, ';')
)
SELECT ID, type_segment AS TYPE
FROM split_cte
WHERE row_num = 1;

Oracle #

Use REGEXP_SUBSTR with a regex pattern that matches the final segment after the last semicolon:

SELECT 
    ID,
    REGEXP_SUBSTR(TYPE, '[^;]+$') AS TYPE
FROM your_table;

The pattern [^;]+$ matches one or more characters that aren't semicolons, anchored to the end of the string—exactly the last item we need.

All these solutions will produce your expected output, handling every row in your sample data correctly.

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