Apache OpenOffice Community Forum

Spreadsheet basis.

SUBSTRING(str,pos,len) returns too many characters when len > length(str), rather than stopping at the end of the string, as would be usual in most other implementations. (Albeit, for all I know, perhaps not in SQL.) In my discovery, chinese characters are returned.

If str is 20 characters then SUBSTRING(<field>,4,16) does the expected.

if str is less, it keeps going ... somewhere.

Sadly, I couldn't do SUBSTRING(<field>,4,MAX(LENGTH(<field>,16))). And no IF in file functions either.

Bummer.

I don't see this behavior when querying a table in an embedded database. Do you see Embedded Database at the lower left of your Base document. What type of field are you working with, a VARCHAR or something else?

If str is 20 characters then SUBSTRING(<field>,4,16) does the expected.

if str is less, it keeps going ... somewhere.

It works for me in OOo 3.3RC8 with fields containing even a 3 character long string.