best fit matching???

[Jamarien]

hello friends,
Here is my query.
I have a table of region codes that i need to search on. e.g. 44207, 441434, 441, 1, 43, 34, etc. I have a telephone number e.g. 441434380000 that i want to match to the 'best fit' number in the table, e.g. 441434 would be the best match in this case.
Can anyone tell me the best way to achieve this? -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[rian]

If you have a simple schema:

CREATE TABLE region_codes (
region_code CHAR(6) NOT NULL
, PRIMARY KEY (region_code)
);

CREATE TABLE phone_numbers (
phone_number VARCHAR(16) NOT NULL
, PRIMARY KEY (phone_number)
);

Try the following:

SELECT region_code
FROM region_codes
WHERE region_code = LEFT(@my_phone_number, LENGTH(region_code))
ORDER BY LENGTH(region_code) DESC
LIMIT 1;

all the best! -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --