Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am using Base in LibreOffice 4.0 (still using the HSQL that comes with Base but looking at moving to HSQL 2.2.8) and I would like to do a query that asks for parameter input like:

SELECT * FROM "Contacts" WHERE "FirstName" LIKE :FirstName

and when you run the query a window pops up asking for your parameter input for 'FirstName'

The problem with this one is that my current client is, well, a terrible speller, and the Parameter input requires an exact match, including capitals.

I tried multiple ways to add the wildcard % symbol to the parameter search and it will not work.

I used SQL to make a wildcard query (eg SELECT * FROM "Contacts" WHERE "FirstName" LIKE 'W%') but unfortunately it requires this client going into the SQL for that query and changing the letters he is searching for, and again this is not an option for my client who is also not proficient in SQL, databases, or computers for that matter.

Is there any way to put a wild card search into a query that produces a parameter input? OR is there some macro I can program (help please!) that will do this for me?

Note: While the search record box does search for the match anywhere in the field this is a very slow search method due to the large number of contacts.

share|improve this question
add comment

2 Answers

https://help.libreoffice.org/Common/Find_Record#Wildcard_expression

You can use vildcards:

? for single wildcard symbol

* for 0-n symbols

?arry can be Harry or Barry

B*b can be Bob or bebob

share|improve this answer
add comment

Haven't tested on hsql 2.2, but this works on hsql 1.8. Perhaps you could use something similar--may have to change the concat to work with ||.

SELECT * FROM "Contacts" WHERE UPPER("FirstName") LIKE CONCAT(UPPER(:FIRSTNAME),'%')
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.