Ask Your Question
0

Is it possible to put a wildcard search into a parameter input query in Base? [closed]

asked 2013-03-04 19:37:36 +0200

Libre Lyrae gravatar image

updated 2015-09-10 23:38:05 +0200

Alex Kemp gravatar image

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. My attempt to use an INDEX to speed the search was also unsuccessful (see my other question regarding this here: http://ask.libreoffice.org/en/question/13784/is-there-a-way-to-create-indexes-that-will/

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 07:40:57.489596

2 Answers

Sort by » oldest newest most voted
0

answered 2014-03-14 02:09:36 +0200

frofa gravatar image

updated 2014-03-14 02:10:11 +0200

Why don't you just make a FORM with a drop-down list (using a list box) where your client could just choose the name (and simply typing in the first letter would also take you to the right section of the drop-down list).

edit flag offensive delete link more
0

answered 2014-03-13 22:24:03 +0200

domike gravatar image

This version:

SELECT * FROM "Contacts" WHERE LOWER("FirstName") LIKE LOWER('%' || :FirstName || '%')

changes your query as follows:

  • it does a case-insensitive comparison, by converting to lowercase before doing the comparison
  • it works even if you insert part of the value to be searched, because it surrounds the user-entered value with '%'
edit flag offensive delete link more

Comments

Be careful with this, at least in LO 5.0.2. It seems to insist on spaces before and after the quoted percent signs for some reason (LO bug?). I found that LIKE LOWER ( '%' || :FirstName || '%' ) works OK. Without the spaces it thinks that the whole thing is a string, encloses it in single quotes and adds an "=" at the front: 'LIKE LOWER( ''%'' || :FirstName || ''%'')' Bizarre.

ptoye gravatar imageptoye ( 2015-11-03 16:18:06 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2013-03-04 19:37:36 +0200

Seen: 2,107 times

Last updated: Mar 14 '14