[Solved] BASE, SELECT syntax with the REGEXP_COUNT()
Posted: Tue Dec 20, 2022 11:01 pm
Hello, I am new to the Base and experimenting with REGEXP functions. Some of them work as expected but some returns warning only.
Please could you have a look at [example 1] below? Thank you.
I use:
Libre Office version 7.4.3.2 (x64) on Windows
#HSQL Database Engine 2.3.2, this shall support REGEXP according this [link 2]
I created 'Split' HSQL database 2.X according this [link 1]
There is one table only "tblPlant". Command is tested directly from menu, Base -> Tools -> Execute SQL Command
Problem:
on the other hand REGEXP_MATCHES works as expected
Similar problem recreated with another built-in function:
I feel, I miss something in syntax or functions might not be implemented in version 2.3.2. I know about the LIKE sql syntax but I use REGEXP to learn its basics usage for future more complex records analysis.
Please could you have a look at [example 1] below? Thank you.
I use:
Libre Office version 7.4.3.2 (x64) on Windows
#HSQL Database Engine 2.3.2, this shall support REGEXP according this [link 2]
I created 'Split' HSQL database 2.X according this [link 1]
There is one table only "tblPlant". Command is tested directly from menu, Base -> Tools -> Execute SQL Command
Problem:
Code: Select all
[example 1]
select REGEXP_COUNT("Name",'^.*old') from "tblPlant"
Returns:
1: user lacks privilege or object not found: REGEXP_COUNTCode: Select all
[example 2]
select REGEXP_MATCHES("Name",'^.*old') from "tblPlant"
Returns:
TRUE,
FALSE,
TRUE,
FALSE,
FALSE,
FALSE,
FALSE,Code: Select all
[example 3]
select "Name" from "tblPlant"
Harold,
Gwen,
Harold,
Benny,
Diane,
Gwen,
Gwen,Code: Select all
[example 4]
select JSON_ARRAYAGG("Name") from "tblPlant"
Returns:
5: user lacks privilege or object not found: JSON_ARRAYAGG