[Solved] Basic wildcard searching
[Solved] Basic wildcard searching
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
Windows 7
Re: Basic wildcard searching
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...
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
Re: Basic wildcard searching
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Basic wildcard searching
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"
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
Re: Basic wildcard searching
In Base you can use both:keme wrote:Note that when"ordinary wildcards" are mentioned in SQL context, we usually mean the SQL wildcards: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.
- underscore for single character
- percent sign for "any number of arbitrary characters"
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice