Solved

Stored Procedure Replace function

Posted on 2009-04-15
7
202 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

810 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