CODEWITHSUNDEEP
sqlsql-server
Kkt2
Kkt2

Reputation: 67

SQL String split and update field

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.

enter image description here

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%'

Upvotes: 0

Views: 932

Answers (2)

Thom A
Thom A

Reputation: 95830

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),'');

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

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)

Upvotes: 3

Related Questions