search for keyword in table

[nickyeng]

i have a table with some column, one of the column is phone number.

so if i want to count the phone number start with 234xxxxxx and 2345xxxxx ...how to do it with sql query statement?

like this,

select count(*) from table1 where phonenumber = 234xxxx and 2345xxxxx
 

what is the keyword for it ?

i'm stuck.
pls help
from
Nick

[amitpatel66]

i have a table with some column, one of the column is phone number.

so if i want to count the phone number start with 234xxxxxx and 2345xxxxx ...how to do it with sql query statement?

like this,

select count(*) from table1 where phonenumber = 234xxxx and 2345xxxxx

what is the keyword for it ?

i'm stuck.
pls help
from
Nick

Check below Queries:

SELECT COUNT(*) FROM table1 WHERE phonenumber LIKE '234%' OR phonenumber LIKE '2345%'

(or)

SELECT COUNT(*) FROM table WHERE SUBSTR(phonenumber,1,3) = '234' OR SUBSTR(phonenumber,1,4) = '2345'

[nickyeng]

Check below Queries:

SELECT COUNT(*) FROM table1 WHERE phonenumber LIKE '234%' OR phonenumber LIKE '2345%'

(or)

SELECT COUNT(*) FROM table WHERE SUBSTR(phonenumber,1,3) = '234' OR SUBSTR(phonenumber,1,4) = '2345'

can i use AND instead of OR ?

or

can i use

Select count(*) form table1 where name = 'nick' and year = '2007' and substr(phonenumber,1,3) = '234' and substr(phonenumber,1,4) = '2345';

[r035198x]

can i use AND instead of OR ?

or

can i use

Select count(*) form table1 where name = 'nick' and year = '2007' and substr(phonenumber,1,3) = '234' and substr(phonenumber,1,4) = '2345';

Guys let's all remember to use [code=mysql] tags when posting our code.
@OP Why don't you try them and see what happens? You'll learn better that way. Reading a tutorial is also not a bad idea.

[amitpatel66]

can i use AND instead of OR ?

or

can i use

Select count(*) form table1 where name = 'nick' and year = '2007' and substr(phonenumber,1,3) = '234' and substr(phonenumber,1,4) = '2345';

only one condiition substr(phonenumber,1,3) = '234' is enough in the query. This will give you all the records whose first 3 digits start with '234'

[nickyeng]

thanks for the tips.

I change it to :

SELECT count(*) FROM table1 WHERE name = 'nick' AND year = '2007' AND (substr(phonenumber,1,3) = '123' OR substr(phonenumber,1,4) = '1234' );
 

and it works fine.
Thanks again.
from
Nick

[amitpatel66]

thanks for the tips.

I change it to :

SELECT count(*) FROM table1 WHERE name = 'nick' AND year = '2007' AND (substr(phonenumber,1,3) = '123' OR substr(phonenumber,1,4) = '1234' );
 

and it works fine.
Thanks again.
from
Nick

Your query will work fine but the last condition in your query is not required.
substr(phonenumber,1,3) = '123' will itself return the phone number which starts with 123. This condition will also return the phone number with 1234 because the first three digits are 123 and it satisfies this condition substr(phonenumber,1,3) = '123'

[pbmods]

Heya, Nick.

Don't use SUBSTR in this query; it's not indexable. Use the LIKE keyword instead.

[nickyeng]

Your query will work fine but the last condition in your query is not required.
substr(phonenumber,1,3) = '123' will itself return the phone number which starts with 123. This condition will also return the phone number with 1234 because the first three digits are 123 and it satisfies this condition substr(phonenumber,1,3) = '123'

what if i have these number :

016
0146
01430
01431

[pbmods]

Heya, Nick.

Depends. What part of those numbers is significant?

[nickyeng]

hi pdmods

my records contains 60thousands and keep increasing...
it has number in each record.

i check the number in each record by that number starts with 016, 0146, 01430, 01431; and increase counter.

and then i have another counter for another group which has to check/search number by starts with 6012, 60146.

This is what i want.

[pbmods]

Heya, Nick.

pdmods. That's a good one.

Make sure your table is indexed by phone number, and then execute your query:

SELECT
        *
    FROM
        `table`
    WHERE
    (
            `phone` LIKE '016%'
        OR
            `phone` LIKE '0146%'
        OR
            `phone` LIKE '01430%'
        OR
            `phone` LIKE '01431%'
    )
 

By making use of the table's index, this query can be executed relatively quickly regardless of the number of rows.