Split string and take last element

sql

functions

best-practices

conditional-checks

byAnton Shumikhin·Jan 4, 2025

Rapidfire solution: To extract the last element from a delimited string in SQL, employ SUBSTRING and CHARINDEX:

SELECT SUBSTRING(column_name, CHARINDEX(delimiter, REVERSE(column_name)) * -1) AS last_element
FROM table_name;

Replace column_name with your field, table_name with your data source, and delimiter with the actual separator character.

Detailed Breakdown

Reversing the strings in the town

Enter the REVERSE function flipping the string, something like: "This is tremendous" to "suoerdne...". Yes, reversing is a strange world!

SELECT REVERSE(column_name)// Town flip, huh?
FROM table_name;

The CHARINDEX navigates our strange world, locates the position of the final delimiter.

Right swipe to the end

With RIGHT function now we have a portion of the string like the end piece of a "live-long" sandwich:

SELECT RIGHT(column_name, CHARINDEX(delimiter, REVERSE(column_name)) - 1) AS last_element //Why go right when you can reverse?
FROM table_name;

Playing with varying delimiters

When the separator has multiple personality disorder, we can do some magic with a CASE statement...abracadabra:

SELECT CASE
    WHEN CHARINDEX('|', column_name) > 0 THEN RIGHT(column_name, CHARINDEX('|', REVERSE(column_name)) - 1)
    WHEN CHARINDEX('-', column_name) > 0 THEN RIGHT(column_name, CHARINDEX('-', REVERSE(column_name)) - 1)
    ELSE column_name  // work.life balance <- there's life after work!
END as last_element
FROM table_name;

Tackling with not so cooperative strings

Then sometimes we have the grumpy strings with no delimiters or only one element. In this case, conditional checks can save your day:

SELECT CASE
    WHEN CHARINDEX(delimiter, column_name) = 0 THEN column_name // Me, Myself and I
    ELSE RIGHT(column_name, CHARINDEX(delimiter, REVERSE(column_name)) - 1)
END as last_element
FROM table_name;