Extracting the last part of a string after a delimiter in SQL Server

A common SQL Server task is pulling the last segment out of a delimited code: REPORT from DATA_CC_001_REPORT, SUMMARY from DATA_SET_001_SUMMARY. SQL Server has no “last index of” function, so every solution works around that, usually with REVERSE. This guide shows three approaches that work, two common versions that look right but return the wrong value, and how to handle strings without the delimiter. All queries and outputs below were run on SQL Server 2022.

Sample data

CREATE TABLE example_data (
    data_string VARCHAR(255)
);

INSERT INTO example_data (data_string) VALUES
('DATA_CC_001_REPORT'),
('DATA_CC_002_SUMMARY'),
('DATA_CC_003_DETAILS'),
('DATA_CC_004_OVERVIEW');

The goal is REPORT, SUMMARY, DETAILS and OVERVIEW.

Option 1: REVERSE + CHARINDEX + SUBSTRING

SELECT
    data_string,
    REVERSE(SUBSTRING(REVERSE(data_string), 1, CHARINDEX('_', REVERSE(data_string)) - 1)) AS extracted_value
FROM
    example_data;

Read it from the inside out:

1. REVERSE(data_string) reverses the string: DATA_CC_001_REPORT becomes TROPER_100_CC_ATAD.
2. CHARINDEX('_', REVERSE(data_string)) finds the first underscore in the reversed string, which is the last underscore in the original.
3. SUBSTRING(REVERSE(data_string), 1, ... - 1) takes everything before that underscore: TROPER.
4. The outer REVERSE turns it back into REPORT.

Option 2: RIGHT (shorter)

The position of the last underscore counted from the end is all you need, because RIGHT takes characters from the end of the original string:

SELECT
    record_name,
    RIGHT(record_name, CHARINDEX('_', REVERSE(record_name)) - 1) AS last_part
FROM
    data_records;

For a table data_records with record_name values like these:

REVERSE('DATA_SET_001_SUMMARY') returns 'YRAMMUS_100_TES_ATAD'; the underscore is at position 8, so RIGHT(record_name, 7) returns SUMMARY.

Option 3: PARSENAME

PARSENAME splits an object name like server.database.schema.object on dots and returns one part by position, counting from the right: part 1 is the last part. Replace the underscores with dots and ask for part 1:

SELECT 
    PARSENAME(REPLACE(column_name, '_', '.'), 1) AS LastPart
FROM 
    your_table;

Replace column_name and your_table with your column and table names.

PARSENAME only handles up to four parts (it was built for four-part object names), so this works for strings with at most three underscores. See the gotchas below.

Versions that look right but aren’t

Reversing before PARSENAME. Because PARSENAME already counts from the right, reversing the string first selects the first segment:

SELECT 
    REVERSE(PARSENAME(REPLACE(REVERSE('DATA_TYPE_001_SAMPLE'), '_', '.'), 1)) AS LastPart;

Output: DATA, not SAMPLE.

Using the reversed position as a start position. CHARINDEX on the reversed string gives the distance from the end, so passing it to SUBSTRING as a start position on the original string starts in the wrong place:

SELECT
    record_name,
    SUBSTRING(record_name, CHARINDEX('_', REVERSE(record_name)) + 1, LEN(record_name)) AS last_part
FROM
    data_records;

For DATA_SET_001_SUMMARY this returns _001_SUMMARY, and for DATA_TYPE_003_EXAMPLE it returns E_003_EXAMPLE. Use option 2 instead.

Gotchas

• No delimiter in the string. CHARINDEX returns 0, the length becomes -1, and options 1 and 2 fail with Invalid length parameter passed to the LEFT or SUBSTRING function. (or ... to the RIGHT function.). Guard it:

  SELECT
      record_name,
      CASE
          WHEN CHARINDEX('_', record_name) = 0 THEN record_name
          ELSE RIGHT(record_name, CHARINDEX('_', REVERSE(record_name)) - 1)
      END AS last_part
  FROM
      data_records;

  PARSENAME returns the whole string when there’s no delimiter.

• The error can’t be caught when values are literals. With constant strings, SQL Server raises the length error (Msg 536) when it compiles the batch, so a surrounding TRY...CATCH never runs.

• PARSENAME and more than four parts. PARSENAME(REPLACE('A_B_C_D_E', '_', '.'), 1) returns NULL.

• PARSENAME and dots in the data. Existing dots become extra separators: for v1.2_FINAL it still returns FINAL, but a value like DATA_v1.2 returns 2.

• Trailing delimiter. TRAILING_ returns an empty string, not NULL.

Related

• Splitting strings and accessing elements in Azure Data Factory does the same in ADF pipeline expressions.
• Splitting strings in Excel: a simple guide covers the spreadsheet version.
• Handling errors in Python: ensuring successful string splits handles the “no delimiter” case in Python.