Error in sql REPLACE

[kkroculick]

Hi,

I have a minor issue with DB2 UDB on Win32. I have a field containing
text data (raw XML). I am trying to replace a string with another
string (via "update table set field0 = replace(field0, 'sometext',
'newtext') to fix some minor application issues. DB2 returns the
following error:

17:13:41 [UPDATE - 0 row(s), 0.000 secs] [Error Code: -443, SQL
State: 38552] Routine "SYSFUN.REPLACE " (specific name "REPLACE2C" ) has
returned an error SQLSTATE with diagnostic text "SYSFUN:10" .
.... 1 statement(s) executed, 0 row(s) affected, execution time 0.000
sec

Not sure if this is cause by a db2 buffer setting or otherwise. Looked
on the web to no avail. Any suggestions would be useful. The column
is long varchar with a length of 37200. I could write an external app
to do data cleaning for this customer but DB2 should be able to handle
this.

Thanks,

Keith

[Fan Ruo Xin]

<kk********@hot mail.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

Hi,

I have a minor issue with DB2 UDB on Win32. I have a field containing
text data (raw XML). I am trying to replace a string with another
string (via "update table set field0 = replace(field0, 'sometext',
'newtext') to fix some minor application issues. DB2 returns the
following error:

17:13:41 [UPDATE - 0 row(s), 0.000 secs] [Error Code: -443, SQL
State: 38552] Routine "SYSFUN.REPLACE " (specific name "REPLACE2C" ) has
returned an error SQLSTATE with diagnostic text "SYSFUN:10" .
... 1 statement(s) executed, 0 row(s) affected, execution time 0.000
sec

Not sure if this is cause by a db2 buffer setting or otherwise.
========
Try "db2 ? sql0443 "
It will tell you the reason - 10 Length or position in string function out
of range
Looked
on the web to no avail. Any suggestions would be useful. The column
is long varchar with a length of 37200. ==========
maximum length of long varchar should be 32700.

I could write an external app
to do data cleaning for this customer but DB2 should be able to handle
this.

Thanks,

Keith

[Twan Kennis]

Hi,

Got the same problem, some days ago.
So I created my own REPLACE-function, which works fine for me:

=============== =============== ========
CREATE FUNCTION fcREPLACE(
arg1 VARCHAR(5000),
arg2 VARCHAR(5000),
arg3 VARCHAR(5000)
)
-- Replaces arg2 with arg3 within arg1

RETURNS VARCHAR(5000)
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL

BEGIN ATOMIC

DECLARE i INT;
DECLARE res VARCHAR(5000) DEFAULT '';

IF arg1 IS NULL THEN
RETURN NULL;
END IF;

SET i = 1;
WHILE i <= LENGTH(arg1) DO
IF SUBSTR(arg1, i, LENGTH(arg2)) = arg2
THEN
SET res = res || arg3;
SET i = i + LENGTH(arg2);
ELSE
SET res = res || SUBSTR(arg1, i, 1);
SET i = i + 1;
END IF;
END WHILE;

RETURN res;
END

=============== =============== ========

Greets, Twan Kennis
SKB Vragenlijst Services
Amsterdam, The Netherlands


"Fan Ruo Xin" <fa*****@sbcglo bal.net> wrote in message
news:Fp******** *********@newss vr17.news.prodi gy.com...

<kk********@hot mail.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

Hi,

I have a minor issue with DB2 UDB on Win32. I have a field containing text data (raw XML). I am trying to replace a string with another
string (via "update table set field0 = replace(field0, 'sometext',
'newtext') to fix some minor application issues. DB2 returns the
following error:

17:13:41 [UPDATE - 0 row(s), 0.000 secs] [Error Code: -443, SQL
State: 38552] Routine "SYSFUN.REPLACE " (specific name "REPLACE2C" ) has returned an error SQLSTATE with diagnostic text "SYSFUN:10" .
... 1 statement(s) executed, 0 row(s) affected, execution time 0.000
sec

Not sure if this is cause by a db2 buffer setting or otherwise.
========
Try "db2 ? sql0443 "
It will tell you the reason - 10 Length or position in string

function out of range

Looked
on the web to no avail. Any suggestions would be useful. The column is long varchar with a length of 37200.

==========
maximum length of long varchar should be 32700.

I could write an external app
to do data cleaning for this customer but DB2 should be able to handle this.

Thanks,

Keith