SQL code for filter tables in Firebird

I’m used to using this syntax when employing filter tables to forms in HSQLDB:

SELECT 
      * 
FROM 
       "tbl_Users" 
WHERE
     ((UPPER("FirstName")    LIKE '%' || UPPER (:Var_FN) || '%' ) OR (:Var_FN  IS NULL))

I cannot get this to work in FireBird, and it seems to have to do with the second part in the WHERE clasue: “OR (:Var_FN IS NULL))”

What’s the alternative? I need a blank field to select all records from the filtered table.

I think the user parameter value must be explicitly CAST as VARCHAR: e.g.

WHERE
 ((UPPER("FirstName") LIKE '%' || UPPER (CAST(:Var_FN) AS VARCHAR(10)) || '%' )
OR
(CAST(:Var_FN) AS VARCHAR(10)  IS NULL))

UPDATE: Below-attached is a sample FB Base file to show a query similar to yours using the above code-technique. To test just run the query qInput_via_user_parameter_UPPERCASE with just ONE upper or lower-case alphabetic letters. Not sure why the above-quoted code didn’t work, but let us know if you have success (or not) with this approach anyway.

search_substring_2_methods

I tried this a bit quickly, but couldn’t make it work. Using LO Base 6.4 on Windows 10.

@frofa,

Your original code works fine with a minor change (also only need to cast for NULL):

WHERE
   "FirstName" LIKE '%' || :Var_FN || '%' 
OR
  CAST((:Var_FN) AS VARCHAR(1))  IS NULL

@Ratslinger,
Thanks for that refinement. Glad you are back on deck.

It looks I was too hasty the first time around, and made some syntax error. I can now get the user input via parameter to work on my own file :slight_smile:

Filtering directly from the filter table looks much cleaner to my eyes, but I cannot make it work for NULL. Doesn’t matter, I’ll go with user parameters in my own work.

with just ONE upper or lower-case alphabetic letters.

Not sure what you mean by this, it works with one or many or a blank field.

Hello,

Please see my answer and comments in this post:

Thank you Ratslinger.

While this solution works, it seems I have to type as many characters into COALESCE as the user might conceivably wish to use for his search term? Considering I have VARCHAR(100) and the user might want to search an arbitrary length of that field - this seems extremely clunky relative to “OR * IS NULL.”

Is there something I’m not understanding correctly?

@paturdc,

No, you have understood it correctly. What you may not have understood is that the situation is a bug (don’t believe it has been reported from the linked post) and the solution is simply a work around.

You example defines a first name and it seems 100 characters is overkill for this. So even if the field is 100 characters, if entry does not exceed 25, then only 25 are needed.

I have in several tables a “Description” field, which in HSQLDB is MEMO, and I think I’ll convert to VARCHAR(100). But I need to be able to search it properly. If this is a workaround, I can certainly live with having 20 or 30 characters max until the bug gets fixed some time in the future.

Thanks for the explanation.

@paturdc,

Code from @frofa works (see sample & my comment). Please change accepted answer to that one.