Quote:
|
Originally Posted by hrdwareguy
The phone number was an example, and to answer franzel's question, yes, the phone number would be treated as an integer so if I had the following numbers:
123-1234
123-1235
123-1237
123-1238
and I searched for 123-1236 it would return 123-1237
other things would be names. Searching on will would return williams, willson etc.
|
zen_tom's suggestion would only work if the table is changed very infrequently, as the entire table would have to be updated for every insert/update of the phone number.
The way I usually solve similar problems:
Assuming your phone numbers use the same formatting and will sort in the way you want as strings (ie if some are '(123) 456 5555' and others are '123-456-6666' the sorting might not be what you want)
Code:
CREATE TABLE employees (
employee_id serial primary key,
phone varchar(14)
);
CREATE INDEX idx_employees_phone ON employees (phone);
you'll need to use two queries to fetch the phones around the nonexistent one
Code:
SELECT employee_id, phone FROM employees WHERE phone > '444-444-4444' ORDER BY phone LIMIT 5;
SELECT employee_id, phone FROM employees WHERE phone < '444-444-4444' ORDER BY phone DESC LIMIT 5;
you can then use either cursors or more queries with different limits/offsets to walk up and down the list.
you'll probably want to normalize the numbers just in case though, one way to do it is to create a function that strips everything but numbers from the string (and returns it as an integer) and then index on that, so:
Code:
CREATE INDEX idx_employees_phone ON employees (strip_alpha(phone));
SELECT employee_id, phone FROM employees WHERE strip_alpha(phone) > strip_alpha('444-444-4444') ORDER BY phone LIMIT 5;