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

# Regular expression in SELECT statement (LibreOffice Base)

Hi all, in table "software"

|ID|App|Note|

|-|-|-|

|0|Writer|text editor|

|2|Draw|drawing|

|3|Impress|presentation|

when exec SELECT * FROM software, I get all records from table. But when exec SELECT * FROM software WHERE Note SIMILAR TO 'r$', I get error 1: firebird_sdbc error: *Dynamic SQL Error *SQL error code = -204 *Table unknown *SOFTWARE *At line 1, column 22 caused by 'isc_dsql_prepare' Where is issue? edit retag close merge delete ## 2 Answers Sort by » oldest newest most voted Thanks for your respond. I try your solution, but same issue. more ## Comments OK. Sorry about that! My "answer" did not address the error message in depth, but only listed what I suspected might be wrong with your query. The error message is not consistent with my findings, but I suspected some error message glitch, perhaps caused by a (syntactically) misplaced $.

Another thought::

Did you double (triple?) check that the table name is correctly written, and there are space separators between the words?

( 2019-11-12 14:59:23 +0200 )edit

Please do not use an answer unless answering the original question (you can answer your own question). Instead use add a comment under answer you are responding to.

Your problem is because of table (and will be for field) names. If the field/table name is not all upper case you need to surround with quotes. This is most likely the statement you need:

SELECT * FROM "software" WHERE "Note" SIMILAR TO 'r\$'


Also you need to turn on Run SQL command directly as the interpreter does not recognize SIMILAR TO.

( 2019-11-12 16:46:23 +0200 )edit

As far as I know:

SQL wildcard searches (LIKE operator) and regular expressions (SIMILAR TO operator) must match the entire data element. Partial matches are not returned. Also, the set of regex tokens for SQL does not match the set of tokens used for LibreOffice applications in general. There are a few differences.

You may want one of these:

 SELECT * FROM software WHERE Note LIKE '%r'
SELECT * FROM software WHERE Note SIMILAR TO '_*r'

more