如何在SQL Server中提取逗号分隔列指定位置的单个值

Hey there! Extracting the 10th element from a comma-separated string in SQL Server is totally doable—here are two solid solutions depending on which version of SQL Server you're using:

Solution 1: For SQL Server 2022 or Azure SQL Database #

If you're running a newer version, you can leverage the STRING_SPLIT function's built-in ordinal parameter (added in 2022) to get the exact position you need. This is clean and efficient:

SELECT
    csv_column,
    -- Extract the 10th element from the comma-separated string
    (SELECT value
     FROM STRING_SPLIT(t.csv_column, ',', 1)
     WHERE ordinal = 10) AS tenth_element
FROM your_table t;

• The third parameter 1 tells STRING_SPLIT to return an ordinal column that tracks the position of each element.
• We filter for ordinal = 10 to grab exactly the 10th value. If a row has fewer than 10 elements, this will return NULL.

Solution 2: For Pre-SQL Server 2022 (Older Versions) #

If you're on an older version where the ordinal parameter isn't available, converting the string to XML is a reliable workaround. This method preserves the order of elements and handles empty values (like the consecutive commas in your examples) perfectly:

SELECT
    csv_column,
    -- Convert the CSV string to XML and select the 10th node
    CAST('<x>' + REPLACE(csv_column, ',', '</x><x>') + '</x>' AS XML).value('/x[10]', 'varchar(100)') AS tenth_element
FROM your_table;

• REPLACE(csv_column, ',', '</x><x>') turns each comma into XML closing/open tags, creating a list of <x> elements (including empty ones for consecutive commas).
• Wrapping that in <x> and casting to XML gives us a structured document, then .value('/x[10]', 'varchar(100)') pulls the 10th element. Adjust the varchar(100) to match the maximum length of your elements if needed.

Both methods work efficiently for thousands of rows—pick the one that fits your SQL Server version!

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