Update and Replace Part of a String

UPDATE and REPLACE part of a string

You don't need wildcards in the REPLACE - it just finds the string you enter for the second argument, so the following should work:

UPDATE dbo.xxx
SET Value = REPLACE(Value, '123', '')
WHERE ID <=4

If the column to replace is type text or ntext you need to cast it to nvarchar

UPDATE dbo.xxx
SET Value = REPLACE(CAST(Value as nVarchar(4000)), '123', '')
WHERE ID <=4

SQL update part of string

Replace it

update blist
set serial_no = replace(serial_no, 'abcd_', 'xyz_')
where city = 'US'
  and serial_no like 'abcd%'

Demo

Update a column value, replacing part of a string

UPDATE urls
SET url = REPLACE(url, 'domain1.example/images/', 'domain2.example/otherfolder/')

Replace part of string in SQL

Quick and dirty but you could just do something like:

-- Update the end bits
UPDATE table
SET field = LEFT('field', LEN('field') -2) + '@24'
WHERE field LIKE '%@1';

-- Update the in between bits
UPDATE table
SET field = REPLACE(field, '@1|', '@24|')
WHERE field LIKE '%@1|%'; -- This where statement is optional due to the nature of the REPLACE.

Otherwise you'll have to look into the magical world of REGEX. If this is something you'd want to run more than once, I'd surely look into that. If this is just a one-time-fix-thingy, meh. It's thursday, I'd call it a valid excuse.

Postgresql: replace part of string on update

The answer is as follows:

UPDATE mytable 
  SET myfield = REPLACE(myfield, 'Job Description - ','');

How to replace part of string in SQL

Use the replace function:

update `table` set `column`= replace (`column`, 'http://example.com/','http://example.com/blog/') where `column`like 'http://example.com/blog/wp-content/uploads%'

SQL Server : how to update with string replace

I would be inclined to use stuff() for this purpose:

update product
    set title = stuff(title, 1, 2, 'za')
    where title like 'mn%';

Update and Replace a start part of string from a column

Can you try this one:

UPDATE myTable
SET myColumn = '/1/2/3/' + REVERSE(SUBSTRING(REVERSE(myColumn),0,CHARINDEX('/',REVERSE(myColumn))))

It should insert /1/2/3/ in front and keep the part after last / from the original column value.

EDIT:

By the way, your query would also work if you removed SELECT FROM part.

UPDATE myTable
SET myColumn = REPLACE(myColumn, SUBSTRING(myColumn, 0, len(myColumn) - CHARINDEX('/',REVERSE(myColumn)) +1), '/1/2/3')

Problem is, your query with select returned all the rows from your table and tried to update your single row value with values from subquery. That's why you got an error.

Update part of a string in SQL

The reason you get an error is probably since option is a reserved word in MySql. to escape reserved words in MySql use this char `:

UPDATE question_table 
SET `option` = Replace(`option`, 'cm2','cm<sup>2</sup>')
WHERE `option` LIKE  '%cm2%'

Here is a list of reserved words in MySql

Here is my favorite method of avoiding the use of reserved words.

---

Related Topics