[Solved] Basic wildcard searching

Creating tables and queries
Post Reply
bwhitman
Posts: 4
Joined: Mon Apr 03, 2017 11:51 pm

[Solved] Basic wildcard searching

Post by bwhitman »

I am new to the OpenOffice Suite and am using Base to help with some analysis of an excel sheet that was used as a text data collector. With many different users entering the data in this 20000+ line spreadsheet there is tremendous variability in the data entry. Is there a way to have Base do wildcard searches without caring about the case? I am trying to avoid having to input an OR statement for each permutation.
Last edited by bwhitman on Fri May 12, 2017 7:57 pm, edited 1 time in total.
OpenOffice 4.1.1
Windows 7
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Basic wildcard searching

Post by keme »

The database engine embedded in OpenOffice is, AFAIK, a version of HSQL-DB. I am not sure whether it is a full, current version or a legacy/barebones version with reduced functionality.

According to HSQLDB documentation, you can use regular expressions in SQL matching.
https://www.tutorialspoint.com/hsqldb/h ... ssions.htm

For English words possibly misspelled there is an additional tool: the SOUNDEX() function.

Soundex is specifically targeted at English pronounciation, but may also work on other languages. I have heard reports of decent results for Dutch and French, and not so good matching for German and Italian. Those are single instance reports of minimal statistical value...
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Basic wildcard searching

Post by Villeroy »

HSQL has a field type VARCHAR_IGNORECASE. You can filter and search by ordinary wildcards.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Basic wildcard searching

Post by keme »

Note that when"ordinary wildcards" are mentioned in SQL context, we usually mean the SQL wildcards:
  • underscore for single character
  • percent sign for "any number of arbitrary characters"
If I recall correctly, MS Access allows the question mark and asterisk wildcards, which makes the queries incompatible with most (perhaps all) non-MS SQL software.
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Basic wildcard searching

Post by Villeroy »

keme wrote:Note that when"ordinary wildcards" are mentioned in SQL context, we usually mean the SQL wildcards:
  • underscore for single character
  • percent sign for "any number of arbitrary characters"
If I recall correctly, MS Access allows the question mark and asterisk wildcards, which makes the queries incompatible with most (perhaps all) non-MS SQL software.
In Base you can use both:
SELECT * FROM "Table" WHERE "foo" LIKE :Starts_With || '%'
SELECT * FROM "Table" WHERE "foo" LIKE :Starts_With || '*'

% and _ are defined in HSQL, Base accepts * and ? as well.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply