Replacing multiple occurances of a character in a column

Posted on 2006-05-12
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
    LVL 6

    Accepted Solution

    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

    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

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

    LVL 6

    Expert Comment

    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

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
    Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    731 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