Can you update specific text within a text field?

I have a table of company projects in which our company name is used repeatedly.  2 problems...
First, our name has changed and
Second, the name includes an ampersand and that is represented in the table three different ways:
1. as "&"
2. as "&"
3. as "&"

Is there a way to parse a text field and replace text automagically - I'm not looking forward to even think about doing this by hand.

For purposes of this question, there are 2 datatype text fields that need to be read - prj_ShortDescription and prj_LongDescription.  Also for the sake of the question, say the company name is 'Peas & Carrots' and it is changing to 'Small Buttered Vegetables'.  

Thus every instance of 'Peas & Carrots', 'Peas & Carrots' and 'Peas & Carrots' in fields prj_ShortDescription and prj_LongDescription would need to be changed to 'Small Buttered Vegetables'.  There might even an extra space before or after the ampersand - I don't suspect this part is possible - I may just have to deal with the stragglers as I find them.

No rush on this - I won't be able to do anything with it until tomorrow morning - some 15+ hours hence.  As always, thanks in advance for any help.
saabStoryAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
a series of UPDATES should do this:
-- replace the &
UPDATE yourtable 
   SET yourfield = REPLACE( yourfield, 'Peas & Carrots', 'Peas & Carrots')
WHERE yourfield LIKE '%Peas & Carrots%' 
-- replace the &
UPDATE yourtable 
   SET yourfield = REPLACE( yourfield, 'Peas & Carrots', 'Peas & Carrots')
WHERE yourfield LIKE '%Peas & Carrots%' 
-- remove the extra space
UPDATE yourtable 
   SET yourfield = REPLACE( yourfield, 'Peas &  Carrots', 'Peas & Carrots')
WHERE yourfield LIKE '%Peas &  Carrots%' 
-- update to the final name
UPDATE yourtable 
   SET yourfield = REPLACE( yourfield, 'Peas & Carrots', 'Small Buttered Vegetables')
WHERE yourfield LIKE '%Peas & Carrots%'

Open in new window

0
 
vs1784Commented:
Use REPLACE function
0
 
vs1784Connect With a Mentor Commented:
Here is Example

It will not replace whole field value, only the matching section.

This is link from MS
http://technet.microsoft.com/en-us/library/ms186862.aspx

Hope it helps,
Thanks
Update 	authors
Set	city = replace(city, 'Salt', 'Olympic')

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
saabStoryAuthor Commented:
Thanks to you both.  I tried both suggestions and got an error because the datatype is text.  So, I change the datatype to varchar(max), the statement parses okay but when I try to execute it, I get an SQL Execution error that says:

"Executed SQL statement: UPDATE dbo.tbl_Projects set prj_ShortDescription = replace(prj_shortDescription, 'Peas & Carrots', 'Small Buttered Vegetables')"
"Error Source: .Net SqlClient Data Provider"
"Error Message: Subquery returned more that 1 value.  This is not permitted when the subquery follows =,!=,<,<=,>,>= or when the subquery is used as an expression."
"The statement has been terminated"

Adding  the WHERE to the statment also validates but gives the same error message.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that looks like you have a trigger on the table?
the UPDATE, as shown, cannot give you that error on it's own.
0
 
saabStoryAuthor Commented:
I was wondering that myself - there is a trigger on a last update column - sorry - forgot it was there.  I've been trying several different ways of working around this.  For some reason, my permissions are out of whack on  our SQL server and I can't alter my own tables, save any dts packages, etc. etc. - so I never know if my problems relate to my profile or to code issues.  That said, I wound up solving my own problem in a back-handed way.  Since we were maintaining the same data in an Access database (long story), I found it was extremely easy to change what I wanted in Access and then import that into SQL.  Not as elegant as a full SQL solution but it gets us down the road.

I'm curious though - is the trigger whats causing the update/replace to fail?  Would removing it fix things?

I'm going to split point 80/20 here.  Got two essentially identical answers but yours was more complete with my specifics in mind.  Plus you did get back to me after my questions.  Thanks for the help - it's much appreciated.
0
All Courses

From novice to tech pro — start learning today.