Extract last two words from my string

Question

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 1

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

Answer 2

You can use PARSENAME (starting with 2012)

DECLARE @Val NVARCHAR(100)= 'AAAA_BBB_CCC_DDDDD'
SELECT PARSENAME(REPLACE(@Val, '_', '.'), 1) -- DDDDD
SELECT PARSENAME(REPLACE(@Val, '_', '.'), 2) -- CCC

Note: This is not correct for more than 3 _