Subject Re: [firebird-support] how to suppress dashes in query results
Author Mark Rotteveel
On 11 Feb 2016 17:57:35 -0800, "homer@... [firebird-support]"
<> wrote:
> Users can enter values with dashes (-) in random locations. I don't know
> where they might put them. I need to select a record using a where
> that doesn't have those dashes. For example, the user might record an
> insurance policy number as 12-345678-01 or 123-456-789-01. All I will
> are the number, and not where they put the dashes. Is there a way to
> suppress the dashes in the Select Clause, or the Where Clause? This is
> being done in a Delphi 2007 program using IBDAC.
> It would be nice if I could do something like ... SELECT * WHERE
> HIDE('-',UsersField) = 'MyValue'
> Or the equivalent in the Select statement.

I'd suggest that you normalize the field content, either in a trigger on
insert/update, or from your application, so that stored values are uniform
in the same format. From that moment on you only need to care about the
normalization of the parameter values used in queries.

With Firebird 3 this might be a little more simple because you would be
able to use PSQL functions to do the same normalization both in the trigger
and in your queries.