[Solved] Queries with Parameter & Wildcard

[Ant]

I want to create a query where the user puts in a search string to find all records containing that search string.

I've used :SearchString as parameter, but when I add wildcards it must treat the parameter as a field as it no longer asks for the parameter value.

The GUI adds apostophes creating 'LIKE %:SearchString%'

Does anyone know how to get around this problem?

[Villeroy]

Not possible by means of built-in methods. You've got to put the wild cards into the parameter to be substituted (or write a macro, but as far as I know from older version 2.0.x, parameter substitution by means of API methods fails too).

[Sliderule]

Just as a follow-up . . . that is . . . for others reading this . . . I wanted to give a graphic example, and, hopefully, a written 'explanation' . . . for clarity.

With a "Parameter Query" . . . the user is 'prompted' to input a 'value' for the search criteria. This is accomplished by starting a 'prompt' on the Criterion line . . . with a semi-colon ( : ).

In the graphic below . . . you can see how this is done . . . with :Last_Name_Percent

Furthermore, the 'prompt' must be one word ( no spaces ) and will be presented to the user, in alphabetical order, when more than one prompt is included in a Query.

According to HSQL documentation http://www.hsqldb.org/doc/guide/ch09.ht ... on-section:

The LIKE keyword uses '%' to match any (including 0) number of characters, and '_' to match exactly one character. To search for '%' or '_' itself an escape character must also be specified using the ESCAPE clause. For example, if the backslash is the escaping character, '\%' and '\_' can be used to find the '%' and '_' characters themselves. For example, SELECT .... LIKE '\_%' ESCAPE '\' will find the strings beginning with an underscore.

So, when entered:

1. Jo% would find all occurrences where the field started with Jo
2. %Jo% would find all occurrences where the field contained Jo anywhere in the field
3. %Jo would find all occurrences where the field ended with Jo

In the instance, where the user is 'prompted' to input a search string . . . via a Parameter Query, the user MUST include the % . . . either, before, after, OR, both before AND after . . . the entered search string. In this case . . . I like to 'prompt' the user to remind them to include the percent character. See below for an example:



Resulting in the following display



I hope this is clear . . . well, at least as clear as mud. Please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.