Problem with decode and clob
bjarkekrMay 27 2010 — edited May 27 2010Hi,
I have a clob field that returns the text "NULL" when empty.
I dont know how to get rid of that, and have tried several methods that doesnt really work. First I tried NVL which doesnt care at all since it seems that the clob is actually a string with the value NULL.
Then I tried decode.. That fails because its only working on varchars it seems.
This is what I then came up with:
decode(to_char(dbms_lob.instr(description, 'NULL')+dbms_lob.getlength(description)), '5', '', description)
(I use both instr and getlength, so that the clob CAN contain the word NULL anywhere but in the start and when its the only word.)
At first I thought it worked.. But as soon as the clob field extends 4000 chars it fails again.
Any hints is appreciated. :)