Solved

remove ampersand from all fields in table

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle RAC 12c 8 60
SYNTAX PROBLEM -- adding another column into the stored procedure output 30 32
Oracle sql query 7 48
use lov values 2 33
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

920 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now