SUBSTRING() returns too many characters

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER

SUBSTRING() returns too many characters

Postby bs27975 » Fri Dec 17, 2010 7:29 pm

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.
OpenOffice 3.3.1 on Windows
bs27975
 
Posts: 78
Joined: Mon May 17, 2010 8:45 am

Re: BUG: SUBSTRING() returns too many characters.

Postby FJCC » Fri Dec 17, 2010 7:41 pm

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?
AOO 3.4 or 4.0 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 3675
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: BUG: SUBSTRING() returns too many characters.

Postby r4zoli » Fri Dec 17, 2010 8:26 pm

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.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
r4zoli
Volunteer
 
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary


Return to External Data Sources

Who is online

Users browsing this forum: No registered users and 3 guests