Replacing multiple occurances of a character in a column

Posted on 2006-05-12
Medium Priority
Last Modified: 2008-02-01
I have a table with following columns and data values:

account             description
0000001            That's when "doubled" & tripled his net value.
0000002            It's very difficult task.
0000003            "that" 'is' very important.

Using a single select statement or within a single select statement how can every occurances of ' (single quote), " (double quotes), & and . (full stop) can be removed in Sybase. I do not remember which sybase version I use at my client site but it does not support user defined functions. Unfortunately sybase does not have any global replace function. Using charindex and stuff sybase functions one can only remove single occurance of the character that needs to be removed.

One solution I can think of is using a cursor to loop each record of the above table and then use a while loop to remove every occurance but I am sure this will drastically reduce the speed.

Overview of the code (syntax not checked):

DECLARE @account VARCHAR(8), @description VARCHAR(100)
Declare cursor
  select account, description from tableA
fetch into       @account ,
while charindex(' ',@description ) > 0
               select @description =substring(@description , 1, charindex(' ',@description )-1)+substring(@description ,  charindex(' ',@description )+1, 255)

insert @account , @description into tableB

fetch into       @account ,

Could somebody help me with a more robust solution that does not use cursor as performance is very important.

Question by:anandgk
  • 2

Accepted Solution

ChrisKing earned 1000 total points
ID: 16672537
I don't think you are going to like the answer, but to be sure, we really need to know which version of Sybase you are on.

You are going to need to write a function, or handle this in your front end.

if you are talking ASE (and I hope you bacause I don't that much about ASA), then version 15 introduced the ability to
create your own functions. Earlier version support java function, but you need to have a java licence (more money)

The easiest way is to just select out the descriptions as they are and massage the data in you front end application.
LVL 29

Expert Comment

ID: 16682388
Agree with ChrisKing.  Depending on the amount of data we are talking about, it may be much easier to just BCP out do a search/replace in a text editor and then BCP in.
LVL 10

Expert Comment

ID: 16709762
Again, depending on your version, the str_replace() function may be available.  It was introduced in ASE version


Expert Comment

ID: 17034829
without confirmation of the version anandgk is running, I feel that bret's answer is equally valid and believe that points should be split

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Applications for our next round of the Experts Exchange Scholarship Contest are starting to roll in. It made us wonder what our past winners are up to these days. Here's a look at what four winners experienced with the contest and what they're doing…
Strategic internal linking is often considered an SEO power technique, especially for content marketing. Do you need to hire an SEO agency to optimize you internal linking? No, this article will help you understand the basics of internal linking and…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

807 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