CODEWITHSUNDEEP
sqlsql-serversubstringcharindex
noob
noob

Reputation: 47

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?

Upvotes: 1

Views: 467

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 82010

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

Upvotes: 1

SinisterPenguin
SinisterPenguin

Reputation: 1618

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)

Upvotes: 3

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22311

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

Upvotes: 2

Related Questions