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

Ask Your Question
0

Regular expression in SELECT statement (LibreOffice Base)

asked 2019-11-12 14:02:47 +0200

GUser gravatar image

updated 2019-11-12 14:03:24 +0200

Hi all, in table "software"

|ID|App|Note|

|-|-|-|

|0|Writer|text editor|

|1|Calc|spreadsheet|

|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 flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2019-11-12 14:49:27 +0200

GUser gravatar image

Thanks for your respond. I try your solution, but same issue.

edit flag offensive delete link 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?

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

@GUser,

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.

Ratslinger gravatar imageRatslinger ( 2019-11-12 16:46:23 +0200 )edit
0

answered 2019-11-12 14:28:16 +0200

keme gravatar image

updated 2019-11-12 14:35:26 +0200

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'
edit flag offensive delete link more

Comments

Yes, I check syntax, but same issue.

GUser gravatar imageGUser ( 2019-11-13 14:44:13 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-11-12 14:02:47 +0200

Seen: 388 times

Last updated: Nov 12 '19