Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 981
  • Last Modified:

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
0
mugsey
Asked:
mugsey
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
update yourtable
  set title = replace(title, '&', 'and')

Open in new window

0
 
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

Featured Post

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.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now