How to do a replace for given example in Sql?

I want to replace always the last node in the string-

root/node1/node2

If I pass node3 as the parameter it should do a replace like this -

 root/node1/node3

Can anyone help me do this say the column name was lineage and I have the id. So, the query would be -

Update
   tree
set
  lineage= -- replace开发者_StackOverflow中文版(lineage,node3)  -- this is what i need  ?
where
  id=2

You could do some string manipulation to find the last occurrence of a /, and then strip everything after that point... and then append your new node parameter to that value

Update
   tree
set
  lineage = LEFT(Lineage, LEN(Lineage) - CHARINDEX('/', REVERSE(Lineage)) + 1) + @NewNode
where
  id=2

User Defined Function. You have the option of T-SQL or .NET languages. I, myself, would chose .NET, as it give the ability to use Regex to more efficiently hone in on the last part of the string, but you can split and reassemble a string using T-SQL. This shows splitting, for example:

http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx