Wildcards in query question

  • Thread starter Thread starter Mark Burnham
  • Start date Start date
M

Mark Burnham

I have a single table that I am trying to create a query for. This shouldn't
be difficult, but I'm finding it is. I've got a couple hundred thousand
records in the table. I am trying to create the query and exclude some of
the records that I don't care about.

This is a table of user internet activity.

I don't want to see
"http://download.windowsupdate.com/v7/windowsupdate/redir/wuredi", but I do
want to see "http://www.overstock.com". (for example.) I'm trying to get
this one to work and then I will add others using the OR operator.

This is my query:
SELECT [20080717].[IP address], [20080717].[Date/Time], [20080717].URL,
[20080717].[Deny/Allow]
FROM 20080717
WHERE ((([20080717].URL)<>"*windowsupdate*")) OR ((([20080717].URL) Not Like
"*windowsupdate*"))
ORDER BY [20080717].[IP address], [20080717].[Date/Time];

What am I doing wrong?

Thanks for your help.
 
Unless you are expecting a URL which is exactly "*windowsupdate*"
(unlikely!) then every URL in your table is not equal to "*windowsupdate*".
The "*" characters are treated literally, not as wildcards, in your first
criterion, because you are not using the Like operator. Therefore, every
record meets the criterion <>"*windowsupdate*" and every record will be
returned.

I don't understand why you have the <>"*windowsupdate*" criterion at all.
Surely the second criterion, using the "like" operator, is the only one you
need?
 
Thank you very much for your reply.

I was trying any way I could to get some type of matching. That was just to
show what I had tried. Obviously my sql is weak. My intent (misguided) was
something like this.

Select everything from the table that does not contain "windowsupdate"
(somewhere in the URL field.)

Do I understand correctly that the wildcards will only work when using the
Like operator?

Again, thank you for your response.
 
I have a single table that I am trying to create a query for. This shouldn't
be difficult, but I'm finding it is. I've got a couple hundred thousand
records in the table. I am trying to create the query and exclude some of
the records that I don't care about.

This is a table of user internet activity.

I don't want to see
"http://download.windowsupdate.com/v7/windowsupdate/redir/wuredi", but I do
want to see "http://www.overstock.com". (for example.) I'm trying to get
this one to work and then I will add others using the OR operator.

This is my query:
SELECT [20080717].[IP address], [20080717].[Date/Time], [20080717].URL,
[20080717].[Deny/Allow]
FROM 20080717
WHERE ((([20080717].URL)<>"*windowsupdate*")) OR ((([20080717].URL) Not Like
"*windowsupdate*"))
ORDER BY [20080717].[IP address], [20080717].[Date/Time];

What am I doing wrong?

Thanks for your help.
Click to expand...

bcap is correct: you do need the LIKE operator. In this case your second
criterion - NOT LIKE "*windowsupdate*" - should work. It's not clear to me why
you're trying to do it both ways.
 
Yes, that's exactly correct: wildcard characters are only wildcard
characters in conjunction with the Like operator. With any other operator,
they are just characters!

I think this is all you need, please post back if it doesn't do what you
intended (n.b. I removed some parentheses merely to save me having to count
them):

SELECT [20080717].[IP address], [20080717].[Date/Time], [20080717].URL,
[20080717].[Deny/Allow]
FROM 20080717
WHERE [20080717].URL Not Like
"*windowsupdate*"
ORDER BY [20080717].[IP address], [20080717].[Date/Time];
 
Back
Top