SQL Server simple find/replace on text string within a table column

Question

Its been quite a while since I've run an SQL query and I need a bit of help.

I have a SQL Server database in which I need to do a string find/replace on text content in column "content" in table named "my_table". I'm getting an "invalid object name" error when I try to execute the SQL:

UPDATE dbo.MY_TABLE
  SET CONTENT = REPLACE(CONTENT, 'http://123.123.123.123', 'http://www.mysite.com')
  WHERE CONTENT LIKE '%http://123.123.123.123%';

The website's html content is stored inside the "my_table" table in a field labeled "Content". I have thousands of instances of the IP address which need to be updated to reflect the new domain name.

I'm connected to the database and looking at the table, inserted the SQL above into the query field and clicked "Execute", then I get the error in the bottom window of SQL Manager — RegEdit
– RegEdit, Commented Jan 30, 2013 at 21:14
Did you try my suggestion -- running: SELECT * FROM dbo.My_Table? — sgeddes
– sgeddes, Commented Jan 30, 2013 at 21:16
Apparently I was in the wrong database. The query works. Thanks! — RegEdit
– RegEdit, Commented Jan 30, 2013 at 21:20

sgeddes · Accepted Answer · 2013-01-30 21:12:05Z

3

Seems to work fine for me:

http://sqlfiddle.com/#!3/451de/1

My guess is that the owner of My_Table isn't dbo? Can you:

SELECT * FROM dbo.My_Table

Good luck.

answered Jan 30, 2013 at 21:12

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

o11c · Accepted Answer · 2015-06-23 23:59:10Z

-1

UPDATE dbo.MY_TABLE
SET CONTENT = example.com --Enter your actual website name
WHERE CONTENT LIKE oldexample.com --enter what address you want replace

edited Jun 23, 2015 at 23:59

o11c

16.5k5 gold badges57 silver badges80 bronze badges

answered Jun 23, 2015 at 23:54

Fumanchu

1

Comments

Domingo Trujillo · Accepted Answer · 2015-07-30 14:32:21Z

-1

The problem is the field name. It is a reserved word. Change content by [content]

answered Jul 30, 2015 at 14:32

Domingo Trujillo

212 bronze badges

1 Comment

spenibus Over a year ago

This seems inaccurate based on this: technet.microsoft.com/en-us/library/ms189822%28v=sql.100%29

Collectives™ on Stack Overflow

SQL Server simple find/replace on text string within a table column

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related