What the manual says.
REPLACE The REPLACE function replaces all occurrences of search-string in
source-string with replace-string. If search-string is not found in
source-string, source-string is returned unchanged. source-string An
expression that specifies the source string. The source-string must be a
built-in numeric or string expression. A numeric argument is cast to a
character string before evaluating the function. For more information on
converting numeric to a character string, see “VARCHAR” on page 395.
search-string An expression that specifies the string to be removed from the
source string. The search-string must be a built-in numeric or string
expression. A numeric argument is cast to a character string before
evaluating the function. For more information on converting numeric to a
character string, see “VARCHAR” on page 395. replace-string An expression
that specifies the replacement string. The replace-string must be a built-in
numeric or string expression. A numeric argument is cast to a character
string before evaluating the function. For more information on converting
numeric to a character string, see “VARCHAR” on page 395. source-string,
search-string, and replace-string must be compatible. For more information
about data type compatibility, see “Assignments and comparisons” on page 88.
The data type of the result of the function depends on the data type of the
arguments. The result data type is the same as if the three arguments were
concatenated except that the result is always a varying-length string. For
more information see “Conversion rules for operations that combine strings”
on page 105. The length attribute of the result depends on the arguments: v
If search-string is variable length, the length attribute of the result is:
(L3 * L1) v If the length attribute of replace-string is less than or equal
to the length attribute of search-string, the length attribute of the result
is the length attribute of source-string v Otherwise, the length attribute
of the result is: (L3 * (L1/L2)) + MOD(L1,L2) where: L1 is the length
attribute of source-string L2 is the length attribute of search-string L3 is
the length attribute of replace-string If the length attribute of the result
exceeds the maximum for the result data type, an error is returned.
REPLACE ( source-string , search-string , replace-string ) REPLACE
Chapter 3. Built-in functions 353
|
|
|
The actual length of the result is the actual length of source-string plus
the number of occurrences of search-string that exist in source-string
multiplied by the actual length of replace-string minus the actual length of
search-string. If the actual length of the result string exceeds the maximum
for the result data type, an error is returned. If any argument can be null,
the result can be null; if any argument is null, the result is the null
value. The CCSID of the result is determined by the CCSID of source-string,
search-string, and replace-string. The resulting CCSID is the same as if the
three arguments were concatenated. For more information, see “Conversion
rules for operations that combine strings” on page 105. Examples v Replace
all occurrences of the character ’N’ in the string ’DINING’ with ’VID’. Use
the CHAR function to limit the output to 10 bytes. SELECT CHAR(REPLACE(
’DINING’, ’N’, ’VID’ ), 10), FROM SYSIBM.SYSDUMMY1 The result is the string
’DIVIDIVIDG’. v Replace string ’ABC’ in the string ’ABCXYZ’ with nothing,
which is the same as removing ’ABC’ from the string. SELECT REPLACE(
’ABCXYZ’, ’ABC’, ’’ ) FROM SYSIBM.SYSDUMMY1 The result is the string ’XYZ’.
v Replace string ’ABC’ in the string ’ABCCABCC’ with ’AB’. This example
illustrates that the result can still contain the string that is to be
replaced (in this case, ’ABC’) because all occurrences of the string to be
replaced are identified prior to any replacement. SELECT REPLACE(
’ABCCABCC’, ’ABC’, ’AB’) ) FROM SYSIBM.SYSDUMMY1 The result is the string
’ABCABC’.
REPLACE
354 DB2
On Fri, Jun 26, 2009 at 9:00 AM, Alan Shore <AlanShore@xxxxxxxx> wrote:
Morning everyone
we have just been informed that a particular domain name is being changed
to some other value
We therefore have to change all the applicable e-mails
Maybe my memory is playing tricks, but I seem to think that there is a
REPLACE function in SQL.
Problem is - I cannot find it.
Or is it a case of using substr with length etc?
Alan Shore
Programmer/Analyst, Distribution
E:AShore@xxxxxxxxxxx <E%3AAShore@xxxxxxxxxxx>
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
midrange.com
