[PLUG] HSQLDB string operations

[John Jason Jordan]

On Mon, 25 Feb 2008 14:33:16 -0800 (PST)
"Michael Rasmussen" <michael at jamhome.us> dijo:

> On Sun, Feb 24, 2008 at 12:49:20PM -0800, John Jason Jordan wrote:
> > > John Jason Jordan wrote:
> > > > I need to sort a query baased on the rightmost three characters of a
> > > > field using HSQLDB, which is what OOo Base uses.
> > >
> > > What's wrong with:
> > >
> > > RIGHT(s,count)
> > >
> > >     returns the rightmost count of characters of s
> > >
> > > ??
> > >
> > > RIGHT(field,3)...
> >
> > Thanks to you and Tim for the suggestions. It turns out that I was
> trying to create the query in OOo Base (which uses HSQL) but run it on a
> Kexi (SQLite3) database to which the Base database was connected.
> Therefore, I needed the SQL to be in SQLite3, not HSQL. Unfortunately,
> SQLite3 can't seem to do it either. Plus SQLite3 has nowhere near the
> capabilities of HSQL.
> 
> Are you sure?  Had I known you were asking about sqlite I would have written:
> 
> What's wrong with:
> 
>   substr(s,-3)
> 
> substr(X,Y,Z)
> substr(X,Y)
>   Return a substring of input string X that begins with the Y-th
>   character and which is Z characters long. If Z is omitted then all
>   character through the end of the string are returned. The left-most
>   character of X is number 1. If Y is negative the the first character
>   of the substring is found by counting from the right rather than the
>   left. If X is string then characters indices refer to actual UTF-8
>   characters. If X is a BLOB then the indices refer to bytes.

More that I failed to say: Yes, I was trying it in SQLite3, but the
data is in Kexi. Kexi uses SQLite3, but only a small portion of the
SQLKIte3 features have been implemented, and they don't yet include
substr. Even though I am writing the query in an OOo base database that
is connected to the Kexi database, the query language I use in Base has
to be SQLite3.

I solved the problem by moving the data to Base where I can use HSQL
queries. The Base GUI does not implement all features of HSQL yet, but
all HSQL features can be used if you write the statement manually. And
you can sort of do it in the GUI, you just have to write the substr
part in the box where you would normally put a field.

Base is pig slow compared to Kexi, but I can't wait for Kexi to
implement the features that I need. Someday maybe Kexi will mature to
the point where it has the features I need and I can move back.