从我的字符串中提取最后两个单词

I trying extract last words from my string.

我尝试从我的字符串中提取最后一个单词

Example

例

Input String : GGG_FFFF_AAAA_BBBBBB_CCC_DDDDD

输入字符串：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

有没有更简单的实现这一点

5 个解决方案

#1

You can use XML:

您可以使用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

#2

You can use PARSENAME (starting with 2012)

你可以使用PARSENAME（从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 _

注意：超过3 _这是不正确的

#3

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)

#4

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

您可以在此处找到SQL拆分功能代码

#5

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))

#1