Why doesn't SQL REVERSE function work in LibreOffice?

[JustAGuy]

I’m trying to reverse the order of characters in a string using SQL in LibreOffice Base. I’ve been searching all over for answers and all I can find is the REVERSE function. I understand Base uses HyperSQL and found the function in the HyperSQL User Guide at http://ihewiki.wustl.edu/wiki/images/e/ea/HSQL.pdf (p.166). The function is supposed to work as follows:

REVERSE ( )
Returns a character string based on with characters in the reverse order. (HyperSQL)

When I try to run this in the Query Design View, it simply will not run. For my example I just did a select on REVERSE (‘ABCD’). I get an error stating: “Access is Denied: REVERSE in statement…”

The error message is: SQL Status: S1000
Error code: -33

If LibreOffice uses HyperSQL, why won’t this function work? Is there an alternative way to do this using SQL?

Thanks for your help.

[frofa]

I am using HSQLDB version 2.3.1 (in ‘split’ database mode), and the REVERSE function works perfectly regardless of whether the Run SQL command directly button is toggled on or off! I’m not sure if the REVERSE function is available in the default HSQLDB 1.8, but might that be your problem if you are using it? I note the guide you link to above is for HSQLDB version 2.1. The guide to HSQLDB version 2.3 is HERE.

[mariosv]

You can run SQL directly without adaptations through Menu/Tools/SQL, while you are in main window of BASE.

In the Query design there is a button [SQL] and in the Menu/Edit/Run SQL command directly.

[iplaw67]

http://www.hsqldb.org/doc/1.8/guide/guide.html#N1251E

Base comes with hsqldb 1.8, which does not have the REVERSE function.