List of SQL functions in HSQLDB embedded database?

[Przemo]

Is there a list of SQL functions that are supported by the HSQLDB embedded database engine? I’m having problems even with functions like CONCAT.

P.S. I want to do a field split from one field containing “1/2016” to 2 fields with “1” and “2016”, so I’ll need functions like TRIM and LOCATE

The HSQLDB embedded database, fedora linux 33 or windows 10, LO 7.1

Update1: Column is TEXT [VARCHAR]

Update2: version info:

HSQL Database Engine 1.8.0.10
Sun Sep 01 22:10:46 IST 2019
version=1.8.0
hsqldb.cache_version=1.7.0
hsqldb.original_version=1.8.0
hsqldb.compatible_version=1.8.0

Update3: the data is in format: number/year. The number is 1 or 2 digits, the year is always 4 digits. No leading/trailing spaces.

Update4: I use the same ods file on 2 systems: windows 10 and linux fedora and I wrongly assumed that if I see HSQLDB on windows it’s the same on linux…

[anon73440385]

Maybe this helps: HyperSQL User Guide - especially Chapter 10. Built In Functions

[Ratslinger]

@anon73440385,

Your link is for v2.6 and the embedded is v1.8 → here. Functions are in Chapter 9. The v2.6 documentation will have items not available within the LO embedded version.

@Przemo,

The wanted functions are probably LOCATE() and LEFT(s,count) or RIGHT(s,count). TRIM is for removing spaces. Please reply with field type if further help is needed or more specifics with what you are attempting with CONCAT.

[anon73440385]

@Ratslinger - of course you are right and I did not crosscheck the version used in LibreOffice (in fact I don’t know how to find out the version used in LibreOffice without some investigation - the only way I found is to unzip odb file and read file properties for version=)

[Ratslinger]

@anon73440385,

This post, How do I display the HSQLDB Embedded version?, contains a macro which will display the database name and version. It has helped me enough to keep on hand.

[Przemo]

@anon73440385 and @Ratslinger Thanks for help! Looks like translating what I know from some proprietary database will take some effort. I’ll post a separate question how to use functions if I can’t find any working examples.

[Ratslinger]

Hello,

The question is a bit hazy and don’t want to simply guess. Since the field you have is a VARCHAR field, the data you have could be anything. Will base this all on the / character. This will strip any spaces from the beginning or end of the data in the field. Then will extract the data to the left of the / and also the data to the right of the /. Using a field named “text_date” here is the SQL:

SELECT DATEID,
   RTRIM(LTRIM ("text_date")) "text_date",
   LEFT (LTRIM ("text_date"),LOCATE ('/',LTRIM ("text_date")) -1) "data_left",
   RIGHT (RTRIM ("text_date"),LENGTH(RTRIM ("text_date")) - LOCATE ('/',RTRIM ("text_date"))) "data_right"
FROM DATETEST

With varying data and spaces left and/or right of the data, here is the result:

Edit:

Noted from further edit in question:

Update3: the data is in format: number/year. The number is 1 or 2 digits, the year is always 4 digits. No leading/trailing spaces.

This works:

SELECT DATEID,
   "text_date",
   LEFT ("text_date",LOCATE ('/',"text_date") -1) "data_left",
   RIGHT ("text_date",4) "data_right"
FROM DATETEST

[Przemo]

I got a working solution using CHAR_LENGTH - 5 instead of LOCATE as I can’t get LOCATE to work on my system on fedora 33. I’ll ask that as a separate question