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.e. something like ( pseudo ):
SELECT "RECORDS".*, "FILTER".* FROM "RECORDS", "FILTER" WHERE ( "FILTER"."TAGS" IS NULL OR CONTAINS_ALL_TAGS_FROM ( "RECORDS"."TAGS" , "FILTER"."TAGS" ) )
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
Version: 5.4.2.2
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