Using Wildcards In MySQL IN Statement

[Zergman]

I have a column that stores 10 digit phone numbers from different provinces.  What i'm trying to do is to extract all phone numbers from 1 province.  This province has 3 different area codes.

 

Here's what I have so far.

<?php
$sql = "
SELECT dslam, COUNT(*) as total
FROM NS_data 
WHERE stn IN ('780%','403%','587%')
AND tdate = '$date'
GROUP BY dslam 
ORDER BY total DESC 
";
$res = mysql_query($sql);
while (list($id, $tot) = mysql_fetch_row($res))
{
    echo "$id - $tot <br />";
}
?>

 

Problem as you can see is apparently you can't use wildcards with IN

WHERE stn IN ('780%','403%','587%')

 

Not sure how to make it work

[trq]

I believe you'll need to use multiple LIKEs.

 

WHERE stn LIKE '780%' || stn LIKE '403%' || stn LIKE '587%'

[Mchl]

You can't.

Use LIKE

WHERE stn LIKE '780%' OR stn LIKE '403%' OR stn LIKE '587%'

[fenway]

In fact, IN() is just shorthand for the optimizer, and just get re-written as ORs -- which is also why you can't use NULL in there, which came up just recently.

[Zergman]

Good stuff everyone, thanks for the advise and info!

 

Problem i'm running into now is that I can't figure out the proper way to do the entire query.

 

This works for grabbing all numbers in the 3 area codes.

SELECT stn, tdate
FROM NS_data
WHERE stn LIKE '780%'
OR stn LIKE '403%'
OR stn LIKE '587%'

 

But when I do this

SELECT stn, 
tdate 
FROM NS_data 
WHERE stn LIKE '780%' 
OR stn LIKE '403%' 
OR stn LIKE '587%'
AND tdate = '$date'

 

It doesn't narrow down by the $date variable which is just set to todays date.

 

I'm guessing its got something to do with using OR's and AND's in the same query.

Suggestions?

 

 

[Zergman]

I think I got it.

 

Did this and it seems to work

SELECT stn, 
tdate 
FROM NS_data
WHERE stn LIKE '780%'
AND tdate = '2010-04-29'
OR stn LIKE '403%'
AND tdate = '2010-04-29'

 

Is this the right way to do it?

[Mchl]

It isn't. Ever heard of operator precedence?

Is 1 + 2 + 3 * 4 same as (1 + 2 +3 ) * 4 ?

 

Think of OR as of  + and AND as *

 

SELECT stn,
tdate
FROM NS_data
WHERE (stn LIKE '780%'
OR stn LIKE '403%'
OR stn LIKE '587%')
AND tdate = '$date'

[Zergman]

It isn't. Ever heard of operator precedence?

Is 1 + 2 + 3 * 4 same as (1 + 2 +3 ) * 4 ?

 

Think of OR as of  + and AND as *

 

SELECT stn,
tdate
FROM NS_data
WHERE (stn LIKE '780%'
OR stn LIKE '403%'
OR stn LIKE '587%')
AND tdate = '$date'

 

Great stuff, thanks Mchl! 

 

I haven't heard of operator precedence before but i'm about to do some reading on it.  Thanks for the info