--- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@...> wrote:

>
> > > I have a column containing mobile phone numbers in the following
> > format
> > > 04XX XXX XXX
> > > I need to search this table efficiently based on a known number in
> > the
> > > format
> > > 04XXXXXXXX
> > > So I need to either create a calculated column or an index which

will

> > > provide efficiency in
> > > SELECT ID FROM TABLE WHERE MYCOLUMN='04XXXXXXXX'
> > >
> > > Does anyone have any ideas of the best way?
> > >
> > > In Delphi I just have a stripspaces and trim function which does the
> > job for
> > > comparison. This is fine when I move thru a subset of the values but
> > I'd
> > > like to grab the number from the entire table efficiently if
> > possible.
> >
> > I may be making assumptions about the phone numbering system in your
> > local area, but can you assume that any number starting with 04 is a
> > mobile and any number not starting with 04 is not?
> >
> > If that assumption is OK,
> >
> > SELECT ID FROM TABLE WHERE MYCOLUMN STARTING WITH '04'
> >
> > With an index on TABLE.MYCOLUMN
> >
> > Adam
>
> Actually they are all mobiles. they all start with 04 so STARTING

WITH '04'

> will return all rows...
> Anyway- I found the F_STRIPSTRING function in the FreeUDFLib and it

works

> perfectly. I can create an index with it if I want or just use it in the
> where clause.
> Hmmm, I wonder where the performance cutoff would be,.. maintaining this
> index Versus querying directly with it.

It seems needlessly complicated to me (and not compatible with 1.5 or
earlier with expression indices).

Starting With uses the index on the underlying field if available, so
unless you need f_stripstring for some other reason, I would avoid it.

The performance cut-off is simply a trade off between the (marginly)
higher cost of inserting, updating or deleting a record vs the
(significantly) lower cost of querying on that field, as well as the
proportion of mobile numbers to other numbers. If 99.99% of numbers
are mobiles, then an index would be a bit pointless. If the query was
required for a report that is run once a year, then an index would be
a bit pointless.

Adam