Prdp Prdp - 2 months ago 10
SQL Question

Extract last two words from my string

I trying extract last words from my string.

Example

Input String :

GGG_FFFF_AAAA_BBBBBB_CCC_DDDDD


Result

╔═══════════╦════════════╗
║ FIRST_COL ║ SECOND_COL ║
╠═══════════╬════════════╣
║ CCC ║ DDDDD ║
╚═══════════╩════════════╝


I have the below code working.

DECLARE @STR VARCHAR(50) = 'GGG_FFFF_AAAA_BBBBBB_CCC_DDDDD'

SELECT
Reverse(LEFT(Stuff(Reverse(@STR), 1, Charindex('_', Reverse(@STR)) + 1 - 1, ''), Charindex('_', Stuff(Reverse(@STR), 1, Charindex('_', Reverse(@STR)) + 1 - 1, '')) - 1)) as FIRST_COL,
Reverse(LEFT(Reverse(@STR), Charindex('_', Reverse(@STR)) - 1)) as SECOND_COL


Is there any simpler to achieve this

Answer

You can use XML:

DECLARE @input nvarchar(max) = 'GGG_FFFF_AAAA_BBBBBB_CCC_DDDDD',
        @x xml

SELECT @x = CAST('<a>'+REPLACE(REVERSE(@input),'_','</a><a>')+'</a>' as xml)


SELECT  REVERSE(@x.value('/a[2]','nvarchar(max)')) as FIRST_COL,
        REVERSE(@x.value('/a[1]','nvarchar(max)')) as SECOND_COL

Output:

FIRST_COL   SECOND_COL
CCC         DDDDD