I am trying to update a existing column value with new value in sql table.

For example, My table has below data

table1:

In the above table I want to update Request Name column value wherever we have Victor, I want to replace with Victor-ID. For example

for ID 1 we have RequestName column value is Victor-123. I want to update it with Victor-ID-123 using Sql. I know we can do it with update sql statement, but if we have lot of data how to achieve that or replace 'Victor' with 'Victor-ID'. Since we might have different values in Request Name column. I want to update only column value with Victor to Victor-ID in table

Any help, I appreciate it

Thank you

CodePudding user response：

Here are two examples:

Example 1:

update table1
set RequestName = REPLACE(RequestName, 'Victor', 'Victor-ID')
where RequestName like 'Victor-%'

Example 2 (this one will let you use this for any name by changing the where clause):

update table1
set RequestName = LEFT(RequestName, CHARINDEX('-', RequestName) - 1)   '-ID-'   RIGHT(RequestName, LEN(RequestName) - CHARINDEX('-', RequestName))
where RequestName like 'Victor-%'

I don't believe example 1 actually requires the where clause though, as it should only affect those with "Victor". However, if you have something like "Victor1-123" that should not be changed, the where clause will prevent that.

Edit: Just occurred to me that example 1 can be adjusted to:

update table1
set RequestName = REPLACE(RequestName, 'Victor-', 'Victor-ID')

This will allow you to eliminate the where clause.

CodePudding user response：

UPDATE [tablename] SET [RequestName] = REPLACE(RequestName,'Victor','Victor-ID')