At 08:58 18/08/2008, you wrote:

>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.

Amen to that. An update or insert trigger that populates an indexed column for use in this search will be hugely more efficient than an expression-based search or maintaining an expression index.

./heLen