[PLUG] HSQLDB string operations

[Michael Rasmussen]

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.


-- 
      Michael Rasmussen, Portland Oregon
    Be appropriate && Follow your curiosity
  The fortune cookie says:
Remember that as a teenager you are in the last stage of your life when
you will be happy to hear that the phone is for you.
		-- Fran Lebowitz, "Social Studies"