remove ampersand from all fields in table



I have sql server 2005 table that has a title field of type nvarchar(50)

An example entry for the title field would be  
   "New York  & Florda"
or
   "Oregon & Minesota"

I want to remove the ampersand and replace with "and so

     "New York  & Florda"

becomes

       "New York and Florida"

How can I do this
mugseyAsked:
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.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
update yourtable
  set title = replace(title, '&', 'and')

Open in new window

0

Experts Exchange Solution brought to you by ConnectWise

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
 
udaya kumar laligondlaTechnical LeadCommented:
check for the size of the field issues also as you can see
in the follwoing script instead of A AND B you will see the result as only A AND due to the size of the field. if there are lot of & in the srting then check before conversion.
declare @N  nvarchar(5)
set @N = 'A & B'
set @N= replace (@N,'&','AND')
select @N
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as small addition to mine:
update yourtable
  set title = replace(title, '&', 'and')
 where title like '%&%'
 
to avoid to update rows which don't have the & in the title... saving transaction log space! 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.