How to impliment the 'Replace' Function(MSSQL Server) in SyBase

[chumki]

How can the functionality of string function 'REPLACE'in MSSQL Server, be achieved in Sybase SQL Server.

 

[JMCraig]

Can you give us a bit more help with REPLACE? I've never used it so I'm just guessing, but string manipulation in Sybase is truly, shall we say, lousy?

Here's the kind of thing I'm guessing you want to do:

Change
this is a string
to
this is a text

What you need is a combination of the following operations:

charindex
finds the beginning index of a substring in a string
substring
returns part of a string
+ (concatenation) operator

So you might end up with something like this:

declare @chardata varchar (255)
select @chardata = 'this is a string'
select substring (@chardata, 1, charindex('string', @chardata) - 1) + 'text'

As you can see, this is pretty ugly. You may find it convenient to create a stored procedure that returns a value in an OUTPUT parameter that does all the nastiness for you by essentially implementing a REPLACE type function.

Another alternative, if you have the Java add-on capability is to create your own REPLACE replacement as a Java class--this is really slick, but the add-on is overpriced IMHO.

Good luck,

J M Craig
nsjmcraig@netscape.net