How to match a subset of tags stored as space-separated string, using embedded HSQLDB? [closed]

asked 2017-12-19 06:54:55 +0100

librebel gravatar image

updated 2020-10-24 12:15:13 +0100

Alex Kemp gravatar image

Hello Everybody,

I have a table field called “TAGS” that contains a space-separated string of tags, much like the way in which we can enter Question tags here at Ask.LibreOffice.

The order of the tags inside each string can be different, e.g. Record 1 has TAGS = “Tag3 Tag1 Tag8”, and Record 2 has TAGS = “Tag8 Tag9 Tag1”. There can be an arbitrary number of tags inside each TAGS string.

I also have a Search / Filter page where the user can enter specific tags to search for. The filter criterium is entered also as a space-separated string, e.g. as “Tag8 Tag1” in this example.

Now i want to make an SQL instruction that retrieves all Records containing all individual tags that the user entered in the Search / Filter page ( e.g. all Records that contain both “Tag1” and “Tag8” inside their TAGS field ).


I’ve been looking for a way to do this in HSQLDB (Embedded version), without having to construct the SQL statement at runtime.

Unfortunately it appears that some of the built-in HyperSQL functions do not work in embedded HSQLDB, such as REGEXP_SUBSTRING_ARRAY() and ARRAY( <query definition> ), both yield an “Access is Denied” error.

My questions are: Can this be done using any built-in HSQLDB functions that work in the embedded version of HSQLDB ?

Or else could there be another way, that is simpler than parsing the criterium string and constructing the entire SQL statement at runtime?

Any advice is greatly appreciated,

With Regards, lib

Build ID: 1:5.4.2-0ubuntu0.17.10.1
CPU threads: 2; OS: Linux 4.13; UI render: default; VCL: gtk3; 
Locale: nl-NL (en_US.UTF-8); Calc: group
edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2021-02-10 05:14:13.246177