[PLUG] HSQLDB string operations

Tim Bruce timb at bruce4.com
Sun Feb 24 01:40:50 UTC 2008


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. The total number of
> characters in this field varies from one record to the next, so I can't
> just tell it to ignore the leftmost X number of characters. I went here:
>
> http://hsqldb.org/web/hsqlDocsFrame.html
>
> And found a list of built in operations, but I can't find a right-string
> (length) type operator. I found a length operator, and a substring
> operator that works from the left for len characters, but no right
> (length) operator.
>
> The problem is that the query I created in the GUI is sorting the data
> on the entire contents of the field, where I need it sorted only on the
> final three characters in the field, ignoring everything before the last
> three characters. Base lets me edit the query in SQL, but I need to
> figure out how to tell it to ignore all characters before the last
> three. 
>
> There has to be a way to do this. There just has to be. Are there any
> HSQLDB experts in here?
> _______________________________________________
> PLUG mailing list
> PLUG at lists.pdxlinux.org
> http://lists.pdxlinux.org/mailman/listinfo/plug
>   

John,

You'll have to build a formula for this and use the "mid" function (the
substring operator). What you want is the starting position to be
(length-3) and the number of characters to be 3. I don't know the exact
format, but hopefully you can figure it out from that.

Be warned that this is VERY performance intensive and could be time
consuming depending on the number of records you're searching.

Tim



More information about the PLUG mailing list