Solved

remove ampersand from all fields in table

Posted on 2009-04-14
3
972 Views
Last Modified: 2012-05-06


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
Comment
Question by:mugsey
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
ID: 24136368
this should do:
update yourtable
  set title = replace(title, '&', 'and')

Open in new window

0
 
LVL 12

Assisted Solution

by:udaya kumar laligondla
udaya kumar laligondla earned 200 total points
ID: 24136454
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24137364
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to recover a database from a user managed backup
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question