Alan McDonald 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'

So you have data in the database like "04XX XXX XXX" (i.e. with internal
spaces) and you have a search term like "04XXXXXXXX" (i.e. no internal
spaces), is that right?

You have two options, as far as I can see:

1. If *all* data in the database has the internal spaces, simply convert
your search query to be in the same format (i.e. add the required
spaces: "SELECT * FROM table WHERE mycolumn='04XX XXX XXX'").

2. If you're not sure whether the data in the database always has the
internal spaces (e.g. some data is "04XX XXX XXX" but some is
"04XXXXXXXX" etc), then I would add a second column to the table,
"mobile_int" which is an integer type and contains a "normalized"
version of the mobile number. Then just do the search on that column,
use the string column only for display.

Any solution that requires you to perform an operation on *every* row
for each query is not going to be fast because it's impossible to use an
index.

Dean.