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

Possible duplicate of [How to split a comma-separated value to columns](http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) — Mladen Oršolić, Sep 28 '16 at 10:28

score 6 · Accepted Answer · answered Sep 28 '16 at 10:31

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

neer · Answer 2 · 2016-09-28T11:12:39.317

3

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 _

edited Sep 28 '16 at 11:12

answered Sep 28 '16 at 10:24

neer

4,031
6
20
34

Length is not fixed it can have more than 4 `_` – Pரதீப் Sep 28 '16 at 10:26
Your answer is better one from others – Sandip - Frontend Developer Sep 28 '16 at 10:54
@SandipPatel - Problem is it will not work when you have more than 3 `_` – Pரதீப் Sep 28 '16 at 11:01
@Prdp I thought You always have 3 `_` – neer Sep 28 '16 at 11:02

score 2 · Answer 3 · answered Sep 28 '16 at 10:28

Declare @Var1  VARCHAR(MAX)='AAAA_BBB_CCC_DDDDDDD'

----SELECT CHARINDEX('_',REVERSE(@Var1))
SELECT RIGHT(@Var1,CHARINDEX('_',REVERSE(@Var1))-1)


-----SELECT REPLACE(@Var1,RIGHT(@Var1,CHARINDEX('_',REVERSE(@Var1))),'')

--SELECT CHARINDEX('_',REVERSE(
--  REPLACE(@Var1,RIGHT(@Var1,CHARINDEX('_',REVERSE(@Var1))),'')
--))

SELECT RIGHT(
REPLACE(@Var1,RIGHT(@Var1,CHARINDEX('_',REVERSE(@Var1))),''),
CHARINDEX('_',REVERSE(
    REPLACE(@Var1,RIGHT(@Var1,CHARINDEX('_',REVERSE(@Var1))),'')
))-1)

score 1 · Answer 4 · answered Sep 28 '16 at 10:35

You can use a user defined custom function as follows

DECLARE @Val NVARCHAR(100)= 'AAAA_BBB_CCC_DDDDD'
;with cte as (
    select id, val, MAX(id) over (partition by 1) maximum 
    from dbo.SQLSplitString(@Val,'_',1,0) t
)
select val from cte where id >= maximum - 1

You can find the SQL split function codes here

score 1 · Answer 5 · edited Sep 28 '16 at 10:40

1

Try this : While storing string, store it as revers that reduces the UN-neccessary calls to make string reverse

DECLARE @STR VARCHAR(50) =REVERSE('GGG_FFFF_AAAA_BBBBBB_CCC_DDDDD')

SELECT REVERSE(SUBSTRING(@STR,0, CHARINDEX('_',@STR))),
       REVERSE(SUBSTRING(@STR,CHARINDEX('_',@STR,CHARINDEX('_',@STR))+1,3))

edited Sep 28 '16 at 10:40

Pரதீப்

91,748
19
131
172

answered Sep 28 '16 at 10:38

Jaydip Jadhav

12,179
6
24
40

1

You need to reverse the result then – Pரதீப் Sep 28 '16 at 10:39

Extract last two words from my string

5 Answers5