Category: ASE: Functions: String Functions

str_replace()

The String Function str_replace() replaces, in an arbitrary string of characters, every occurrence of a substring with an alternative string. The respective strings are specified in the parameters of the function. All three strings must be of the same datatype. For the corresponding return values, the following is valid:

• If one of the three character strings is defined as datatype char or varchar, the return value is of datatype varchar.
• If one of the three character strings is defined as datatype unichar or univarchar, the return value is of datatype univarchar.
• If one of the three character strings is defined as datatype binary or varbinary, the return value is of datatype varbinary.

• If all parameters are defined with variables of constant and known length, ASE calculates the length of the resulting string as:

      result_length= ((o/p)*(r-p)+o)
      where
      o = length of the original string
      p = length of the search pattern
      r = length of the replacement string
      If (r-p) is less or equal 0, the result length will be equal to u.
      

• The same formula is used, if the original string is a column name, and the other two strings consist of constants with known values at compile time.
• If ASE is not able to calculate the length of the resulting character string, because the values of the parameters were not known at compile time, a default length of 255 is assumed, unless traceflag 244 is set, in which case this value increases to 16384. This is also the maximum value.

Syntax of the String Function str_replace()

    str_replace( string_expression_1, string_expression_2, string_expression_3)
    

The Parameters of the String Function str_replace()

"string_expression_1"
The parameter "string_expression_1" is the original character string, i.e. the string to be searched using the function str_replace. The parameter must be set in single or double quotes. It can consist of column names, variables or constants and must be expressed in one of the datatypes char, varchar, unichar, univarchar, varbinary or binary. If this parameter is defined as NULL, the return value of the function will also be NULL.

"string_expression_2"
The parameter "string_expression_2" corresponds to the search pattern to be found in the original string ("string_expression_1"). The parameter must be set in single or double quotes. It can consist of column names, variables or constants and must be expressed in one of the datatypes char, varchar, unichar, univarchar, varbinary or binary. If this parameter is defined as NULL, the return value of the function will also be NULL.

"string_expression_3"
The parameter "string_expression_3" is the replacement pattern, with which all occurrences of the search pattern ("string_expression_2") are substituted in the orginial string ("string_expression_1"). The parameter must be set in single or double quotes. It can consist of column names, variables or constants and must be expressed in one of the datatypes char, varchar, unichar, univarchar, varbinary or binary. If this parameter is defined as NULL, all occurrences of the search pattern ("string_expression_2") are deleted from the original string ("string_expression_1").

Example for the String Function str_replace()

    select str_replace ("uvwxyzx","x","a") 
    go
        
    -------
    uvwayza
    

In the return string, all "x" of the original string "uvwxyzx" have been replaced by "a".

    select str_replace ("uvwxyzx","x",NULL) 
    ----------
    uvwyz 
    

In the return string, all "x" of the original string "uvwxyzx" have been deleted.

See also:

ASE T-SQL - Aggregate Functions,
ascii(),
char(),
char(n),
charindex(),
char_length(),
compare(),
difference(),
lower(),
ltrim(),
patindex(),
replicate(),
reverse(),
right(),
rtrim(),
sortkey(),
soundex(),
space(),
str(),
String Functions,
stuff(),
substring(),
to_unichar(),
uhighsurr(),
ulowsurr(),
upper(),
uscalar().