MySQL Forums

Hi, i'm sql newbies, hope someone can help.

I'm trying to do a tariff lookup table for my voip service. When a user makes a call, i capture the destination phone number which has the country code prefix (the prefix may have or not have the plus sign). I'm trying to create a SELECT statement which i can use to query the rates table (see structure below) using the country code, and obtain the TARIFF.

For example, in my rate tables, i've got the tariffs for calls to the US (country code 1 or +1) and to Bahamas (country code +1 242 or 1242).

So, if a call goes to +1800xxxxxxxxx or 1800xxxxxxxxx, I would have to assume that the call goes to the US which carries a tariff of 1.30 cents. If the calls goes to +1242xxxxxxxxx, I will have to assume that the call goes to the Bahamas which carries a tariff of 1.95 cents.

I tried the following but they don't work:

SELECT TARIFF from RATES where 1800xxxxxxxxx like COUNTRYCODE%
Or the other way round:
SELECT TARIFF from RATES where COUNTRYCODE% like 1800xxxxxxxxx
Plus a lot others.

Would appreciate any pointers. :)

============================================

CREATE TABLE `rates` (
`RATESID` int(4) NOT NULL auto_increment,
`COUNTRYNAME` varchar(50) NOT NULL,
`COUNTRYCODE` varchar(3) NOT NULL,
`PREFIXRANGE` varchar(200) default NULL,
`TARIFF` decimal(10,2) NOT NULL default '1.00',
PRIMARY KEY (`RATESID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `rates` (`RATESID`, `COUNTRYNAME`, `COUNTRYCODE`, `PREFIXRANGE`, `TARIFF`) VALUES
(1, 'UNITED STATES', '1', NULL, '1.30');

INSERT INTO `rates` (`RATESID`, `COUNTRYNAME`, `COUNTRYCODE`, `PREFIXRANGE`, `TARIFF`) VALUES
(2, 'BAHAMAS', '1242', NULL, '1.95');