Solved

Stored Procedure Replace function

Posted on 2009-04-15
7
186 Views
Last Modified: 2013-11-05
If I have a simple select statement like this:
Select Name from tblNames where ID = 1
I want to replace any occurances in Name with the following:
Replace & with &
Replace " with "
Replace - with –
Is there an easy way to do this in a stored procedure?
0
Comment
Question by:LATurk
  • 5
  • 2
7 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24147391
select replace(name,'&','&')
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24147403
you can do like this

create proc replaceReturn
as
Select replace(Name,'&','&') as name from emps
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24147423
refined version for you.



create proc replaceReturn

as

Select replace(replace(replace(Name,'&','&'),'"','"'),'-','&ndash') as name from tblNames where id=1

Open in new window

0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:LATurk
ID: 24147453
But what if one name had all three in it.  
For Example: John "the man" & Smith-Jones
I know that's ridiculous but I want to be sure none of the characters are in this field.
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
ID: 24147482
it will work for sure, see my last post. i did it practically and it worked
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24147492
see this is what you want, right?

Select replace(replace(replace('John "the man" & Smith-Jones','&','&'),'"','"'),'-','&ndash')

will be looked like

John "the man" & Smith&ndashJones
0
 

Author Closing Comment

by:LATurk
ID: 31570401
Thanks!  Works perfectly!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

762 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

20 Experts available now in Live!

Get 1:1 Help Now