Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

remove ampersand from all fields in table

Posted on 2009-04-14
3
Medium Priority
?
979 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1200 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 800 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

688 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