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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vs1784Commented:
Use REPLACE function
0
vs1784Commented:
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
Guy Hengel [angelIII / a3]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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.