Re: char_length() returns -1 ✗ Answered: red (solid confidence) — The only captured message (quoting the original question about char_length() returning -1 on malformed UTF-8 lvarchar data) explicitly declines to explain the meaning of -1 and defers to IBM support; it does give a secondary, useful answer about the performance cost of a CHECK(LENGTH(...)>=0) constraint.

Advisory only.

klimoffl@hotmail.com wrote: > Hi. I cannot find it documented anywhere what it means when the > function char_length() returns -1 . > > We are working with IDS version 9.4 FC5 (on a Tru64 system). > > I have a table called page_facts with the following columns > > integer page_id, > lvarchar title(500) > > CLIENT_LOCALE and DB_LOCALE are both set to "en_us.utf8" > > if I run the following query: > > select * from page_facts where char_length(title) = -1> > I get several records returned. I have searched high and low for what > that "-1" means - I'm assuming it means that there is malformed utf-8 > in the title column. In fact I saw one posting to this group where > somebody put a check constraint on a table that checked that the > char_length() of a varchar column was greater than 0. But I don't see > it documented anywhere what "-1" actually means. I don't think LENGTH is supposed to return a negative number. Contact IBM Informix Tech Support. > BTW - is putting a constraint like that on my tables a good idea? What > would be the performance impact if the lvarchar column had a maximum > size of 25000 bytes (as one of my tables does). If you mean a constraint such as CHECK(LENGTH(title) >= 0)? If it's allowed (not a foregone conclusion - you couldn't use UPPER, as a counter-example), then it would slow the performance a little on insert or update. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/