sqlsql-serversubstringcharindex

Negative indexing with charindex() and substring function

I have a column that holds the data in the following format:

Field Name
123_456_ABC_DEF
12_34_456_XYZ_PQR
LMN_OPQ_123_456

In each case I require, the last two block of data i.e.

ABC_DEF
XYZ_PQR
123_456

Is there a way to use charindex() in manner where it counts '_' from the right side of the string?

Solution

  • Here's an unreadable & slightly mad way of doing it :-)

    -- DDL and sample data population, start
DECLARE @tbl TABLE (tokens VARCHAR(256));
INSERT @tbl VALUES
('123_456_ABC_DEF'),
('12_34_456_XYZ_PQR'),
('LMN_OPQ_123_456');

SELECT REVERSE(LEFT(REVERSE(tokens),CHARINDEX('_',REVERSE(tokens),CHARINDEX('_',REVERSE(tokens))+1)-1))
FROM @tbl

    Basically reversing the text, searching forwards & reversing it back at the end....(SQL Server T-SQL)