SQL SELECT question.

[joshua__lim]

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');

Would I be able to achieve what I want using SQL? Grateful for any tip. :)

[darkagn]

Looks like COUNTRYCODE is a VARCHAR(3) (that is a maximum of 3 character string) so I don't think you can store the Bahamas code in that field. However if you want all country codes that start with a 1, the following may help:

SELECT TARIFF from RATES where COUNTRYCODE like '1%'

[joshua__lim]

Looks like COUNTRYCODE is a VARCHAR(3) (that is a maximum of 3 character string) so I don't think you can store the Bahamas code in that field. However if you want all country codes that start with a 1, the following may help:

SELECT TARIFF from RATES where COUNTRYCODE like '1%'

Thanks, yes, that's a typo in my test case.

SELECT TARIFF FROM RATES WHERE COUNTRYCODE LIKE '1%'

This SQL statement is correct, but won't work for my case, as my input is not '1%', but the raw phone number with the country code, e.g., 1800xxxxxxxxx -> this is a number for the USA. I'm trying to figure out how can I compare a raw phone number with the table of countrycodes.

So for the case of 1800xxxxxx, it must match with the record:

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

[darkagn]

Ah ok, sorry I misunderstood what you were trying to do.

The SUBSTR() function might be able to help.

SELECT TARIFF FROM RATES WHERE COUNTRYCODE = SUBSTR(<phonenumber>, 0, 3)

You probably have to fiddle with it, but basically this will return the tariffs for all countrycodes that match the first three digits of the phone number. Again, this is assuming that the length of the country code is 3. You may need an if-statement and select country codes of length 2 if no match is made on the first three and finally the first digit only if no match is found there.

[saher_omer]

hello all i want to ask if there examples about select statment i have lab exam in sunday

[darkagn]

Hi saher_omer,

In future, please start a new thread if your question is not directly related to the original poster's problems.

But to answer your question, there are many examples on the net of an SQL select statement. Google will provide millions of them. Here is a good one.

[joshua__lim]

Ah ok, sorry I misunderstood what you were trying to do.

The SUBSTR() function might be able to help.

SELECT TARIFF FROM RATES WHERE COUNTRYCODE = SUBSTR(<phonenumber>, 0, 3)

You probably have to fiddle with it, but basically this will return the tariffs for all countrycodes that match the first three digits of the phone number. Again, this is assuming that the length of the country code is 3. You may need an if-statement and select country codes of length 2 if no match is made on the first three and finally the first digit only if no match is found there.

Thanks darkagn, this is the solution. I'm now using the following:

SELECT TARIFF into gettariff
FROM RATES
WHERE COUNTRYCODE LIKE SUBSTRING(phonenumber,1,3);
IF gettariff>0 THEN return(gettariff);
END IF;