Threaded Mode | Linear Mode

crazYoga · 02-02-2010, 02:18 AM

Function
Replaces all occurrences of a substring with another substring.

Syntax

REPLACE ( original-string, search-string, replace-string )

Parameters
If any argument is NULL, the function returns NULL.

original-string

The string to be searched. This string can be any length.

search-string

The string to be searched for and replaced with replace-string. This string is limited to 255 bytes. If search-string is an empty string, the original string is returned unchanged.

replace-string

The replacement string, which replaces search-string. This can be any length. If replace-string is an empty string, all occurrences of search-string are deleted.

Examples
The following statement returns the value xx.def.xx.ghi.

Code:

SELECT REPLACE( 'abc.def.abc.ghi', 'abc', 'xx' ) FROM iq_dummy

The following statement generates a result set containing ALTER PROCEDURE statements which, when executed, repair stored procedures that reference a table that has been renamed. (To be useful, the table name needs to be unique.)

Code:

SELECT REPLACE(

    replace(proc_defn,'OldTableName','NewTableName'),

    'create procedure',

    'alter procedure')

FROM SYS.SYSPROCEDURE

WHERE proc_defn LIKE '%OldTableName%'

Use a separator other than the comma for the LIST function:

Code:

SELECT REPLACE( list( table_id ), ',', '--')

FROM  SYS.SYSTABLE

WHERE table_id <= 5

Standards and compatibility

SQL/92

Vendor extension.

Sybase

Compatible with Adaptive Server Enterprise.

Ref: Sybase Inc

Possibly Related Threads...
Thread:		Author	Replies:	Views:	Last Post
	String concatenation operator in Sybase T-SQL	Joshi	0	159	02-03-2010 11:06 PM Last Post: Joshi
	SQL to Replace String in Sybase	john	0	359	12-09-2009 05:15 PM Last Post: john
	Replace string in Sybase	Joshi	0	79	09-09-2009 01:49 AM Last Post: Joshi
	sybase string functions with examples	john	0	197	07-07-2009 02:30 AM Last Post: john
	Sybase create function	zaadmin	0	78	07-05-2009 06:42 PM Last Post: zaadmin
	sybase dateadd function	Joshi	0	748	03-12-2009 11:00 PM Last Post: Joshi
	T-SQL: A Simple Way to Build a String from a Column	zaadmin	0	145	10-10-2008 09:50 PM Last Post: zaadmin