Negative indexing with charindex() and substring function

Question

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?

Answer 1

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)

Answer 2

Please try the following solution.

It is based on tokenization via XML and XQuery.

Notable points:

• CROSS APPLY is tokenizing input as XML.
• The XPath predicate [position() ge (last()-1)] gives us last two tokens.

SQL

-- 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');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '_';

SELECT t.*
    , REPLACE(c.query('data(/root/r[position() ge (last()-1)])').value('.', 'VARCHAR(256)')
        , SPACE(1), @separator) AS Result
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
      REPLACE(tokens, @separator, ']]></r><r><![CDATA[') + 
      ']]></r></root>' AS XML)) AS t1(c);

Output

tokens	Result
123_456_ABC_DEF	ABC_DEF
12_34_456_XYZ_PQR	XYZ_PQR
LMN_OPQ_123_456	123_456

Answer 3

Just another option using JSON and a CROSS APPLY

Example

Select NewValue = reverse(JSON_VALUE(JS,'$[0]')+'_'+JSON_VALUE(JS,'$[1]'))
 From  YourTable A
 Cross Apply (values ('["'+replace(string_escape(reverse([Field Name]),'json'),'_','","')+'"]') ) B(JS)

Results

NewValue
ABC_DEF
XYZ_PQR
123_456