How to replace a string in a SQL Server Table Column

Question

I have a table  SQL Sever  which references paths  UNC or otherwise   but now the path is going to change    In the path column  I have many records and I need to change just a portion of the path  but not the entire path  And I need to change the same string to the new one  in every record   How can I do this with a simple update

User · Answer

all answers are great but I just want to give you a good example

select replace('this value from table', 'table',  'table but updated')

this SQL statement will replace the existence of the word "table" (second parameter) inside the given statement(first parameter) with the third parameter

the initial value is this value from table but after executing replace function it will be this value from table but updated

and here is a real example

UPDATE publication
SET doi = replace(doi, '10.7440/perifrasis', '10.25025/perifrasis')
WHERE doi like '10.7440/perifrasis%'

for example if we have this value

10.7440/perifrasis.2010.1.issue-1

it will become

10.25025/perifrasis.2010.1.issue-1

hope this gives you better visualization

User · Answer

select replace ImagePath               as NewImagePath from tblMyTable       where  ImagePath  is my column Name   NewImagePath  is temporery   column Name insted of  ImagePath       is my current string  old   string        is my requried string  new string     tblMyTable  is my table in database

User · Answer

you need to replace path with the help of replace function   update table name set column name   replace column name   oldstring    newstring     here column name refers to that column which you want to change   Hope it will work

User · Answer

You can use this query   update table name set column name   replace  column name    oldstring    newstring   where column name like  oldstring

User · Answer

UPDATE CustomReports Ta SET vchFilter   REPLACE CAST vchFilter AS nvarchar max       Ingl-report Templates    C  Customer Templates   where CAST vchFilter AS nvarchar max   LIKE     Ingl-report Templates     Without the CAST function I got an error   Argument data type ntext is invalid for argument 1 of replace function

User · Answer

I tried the above but it did not yield the correct result  The following one does   update table set path   replace path   oldstring    newstring   where path    oldstring

User · Answer

If target column type is other than varchar nvarchar like text  we need to cast the column value as string and then convert it as   update URL TABLE set Parameters   REPLACE   cast Parameters as varchar max     india    bharat   where URL ID  150721 013359670

User · Answer

It s this easy   update my table set path   replace path   oldstring    newstring

User · Answer

You also can replace large text for email template at run time  here is an simple example for that   DECLARE  xml NVARCHAR MAX  SET  xml   CAST  SELECT  column  AS  td                        StartDate  AS  td           FROM  table            FOR XML PATH  tr    ELEMENTS   AS NVARCHAR MAX   select REPLACE  EmailTemplate      xml     xml  as Newtemplate  FROM  dbo   template  where id   1

User · Answer

UPDATE  table  SET  column    REPLACE  column     foo      bar

[sql] How to replace a string in a SQL Server Table Column

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to database-administration