Jeremy Jeremy - 2 months ago 9
MySQL Question

MySQL match area code only when given the full number

I have a database that lists a few area codes, area code + office codes and some whole numbers and a action. I want it to return a result by the digits given but I am not sure how to accomplish it. I have some MySQL knowledge but its not very deep.

Here is a example:

match | action
_____________________
234 | goto 1
333743 | goto 2
8005551212| goto 3
234843 | goto 4


I need to query the database with a full 10 digit number -

query 8005551212 gives "goto 3"
query 2345551212 gives "goto 1"
query 3337431212 gives "goto 2"
query 2348431212 gives "goto 4"


This would be similar to the
LIKE
selection, but I need to match against the database value instead of the query value. Matching the full number is easy,

SELECT * FROM database WHERE `match` = 8005551212;


First the number to query will always be 10 digits, so I am not sure how to format the SELECT statement to differentiate the match of
234XXXXXXX
and
234843XXXX
, as I can only have one match return. Basically if it does not match the 10 digits, then it checks 6 digits, then it will check the 3 digits.

I hope this makes sense, I do not have any other way to format the number and it has to be accomplished with just a single SQL query and return over a ODCB connection in Asterisk.

Answer

Try this

SELECT match, action FROM mytable WHERE '8005551212' like concat(match,'%')

The issue is that you will get two rows in one case .. given your data..

SELECT action 
FROM mytable 
WHERE '8005551212' like concat(match,'%')
order by length(match) desc limit 1

That should get the row that had the most digits matched..