We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

List of SQL functions in HSQLDB embedded database?

asked 2021-02-26 15:43:02 +0200

Przemo gravatar image

updated 2021-02-27 18:27:56 +0200

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...

edit retag flag offensive close merge delete

Comments

1

Maybe this helps: http://www.hsqldb.org/doc/guide/ - especially Chapter 10. Built In Functions

Opaque gravatar imageOpaque ( 2021-02-26 15:53:06 +0200 )edit
2

@Opaque,

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.

Ratslinger gravatar imageRatslinger ( 2021-02-26 16:52:50 +0200 )edit
1

@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=)

Opaque gravatar imageOpaque ( 2021-02-26 17:10:08 +0200 )edit
1

@Opaque,

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.

Ratslinger gravatar imageRatslinger ( 2021-02-26 17:41:09 +0200 )edit

@Opaque 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.

Przemo gravatar imagePrzemo ( 2021-02-26 17:56:48 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2021-02-26 20:38:49 +0200

Ratslinger gravatar image

updated 2021-02-26 22:55:34 +0200

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:

image description

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
edit flag offensive delete link more

Comments

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

Przemo gravatar imagePrzemo ( 2021-02-27 17:29:55 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-02-26 15:43:02 +0200

Seen: 85 times

Last updated: Feb 27