SQL String split and update field

Question

I have table Project with a column name Name with values in the format SYS_12345_Value. I want to update this Name field such that its value in every row is replaced by the term after second _ in its value.

At the moment it looks like SYS_82058_INDIGO and I want to replace it with INDIGO and the same for all the rows in the table.

Any help is appreciated. Thanks alot.

UPDATE : Tried @GordonLinoff's solution as follows

UPDATE Project
SET Name = (select right(str, charindex('_', reverse(str)) - 1) from (values (Name)) v(str))
WHERE Name like '%SYS%'

Gordon Linoff · Accepted Answer · 2020-11-25 17:16:39Z

3

String manipulation in SQL Server is usually tricky. But if you want the last component, you can use:

select *,
       right(str, charindex('_', reverse(str)) - 1)
from (values ('SYS_82058_INDIGO')) v(str)

answered Nov 25, 2020 at 17:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thom A · Accepted Answer · 2020-11-25 17:17:05Z

1

Use a couple of nested CHARINDEX functions. This assumes that every row has 2 underscore (_) characters:

UPDATE dbo.YourTable
SET YourColumn = STUFF(YourColumn,1,CHARINDEX('_',YourColumn,CHARINDEX('_',YourColumn)+1),'');

answered Nov 25, 2020 at 17:17

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Collectives™ on Stack Overflow

SQL String split and update field

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related