9

I have a CLOB(2000000) field in a db2 (v10) database, and I would like to run a simple UPDATE query on it to replace each occurances of "foo" to "baaz".

Since the contents of the field is more then 32k, I get the following error:

"{some char data from field}" is too long.. SQLCODE=-433, SQLSTATE=22001

How can I replace the values?

UPDATE: The query was the following (changed UPDATE into SELECT for easier testing):

SELECT REPLACE(my_clob_column, 'foo', 'baaz') FROM my_table WHERE id = 10726

UPDATE 2 As mustaccio pointed out, REPLACE does not work on CLOB fields (or at least not without doing a cast to VARCHAR on the data entered - which in my case is not possible since the size of the data is more than 32k) - the question is about finding an alternative way to acchive the REPLACE functionallity for CLOB fields.

Thanks, krisy

krisy
  • 1,508
  • 1
  • 14
  • 32

2 Answers2

1

Finally, since I have found no way to this by an SQL query, I ended up exporting the table, editing its lob content in Notepad++, and importing the table back again.

krisy
  • 1,508
  • 1
  • 14
  • 32
0

Not sure if this applies to your case: There are 2 different REPLACE functions offered by DB2, SYSIBM.REPLACE and SYSFUN.REPLACE. The version of REPLACE in SYSFUN accepts CLOBs and supports values up to 1 MByte. In case your values are longer than you would need to write your own (SQL-based?) function.

BTW: You can check function resolution by executing "values(current path)"

data_henrik
  • 16,724
  • 2
  • 28
  • 49