Solved

remove ampersand from all fields in table

Posted on 2009-04-14
3
968 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 142

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:udayakumarlm
udayakumarlm 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 142

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

776 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