[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

Using LIKE in a query, having trouble with semicolons?

I have a simple access log as a table in SQL Server, Access 2000 front end, Jet engine.
I am trying to filter out the entries that are just bot hits.
My SQL looks like this:
SELECT LOG.ACCESS_DATE, LOG.CLIENT_IP, LOG.QUERY_STRING, LOG.CLIENT_BROWSER
FROM ZZ_ACCESS_LOG as LOG
WHERE (LOG.CLIENT_BROWSER) NOT Like "%bot%";
I have also tried this with *. It doesn't make a difference. Either way, this only filters out some of the entries with "bot" in them. I think it gest tripped up by semicolons in the text. Here is an example of a string it failed to filter:
Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)

Can anyone explain what is happening to me, and how to fix it?
0
DataCurator
Asked:
DataCurator
  • 9
  • 6
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the data type of LOG.CLIENT_BROWSER?
if it's varchar/nvarchar, what collation is it?
0
 
DataCuratorAuthor Commented:
It's nvarchar(255), the collation field says <database default>. If I click the option open, it looks like it is resting at SQL_Latin1_General_CP1_CI_AS.

I don't know what that means (I'm new to SQL Server), so let me know if you're looking for something else.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this:
SELECT LOG.ACCESS_DATE, LOG.CLIENT_IP, LOG.QUERY_STRING, LOG.CLIENT_BROWSER
FROM ZZ_ACCESS_LOG as LOG
WHERE LOG.CLIENT_BROWSER NOT LIKE '%bot%'

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
DataCuratorAuthor Commented:
No dice--still doesn't filter correctly. It's functionally the same as the Query I posted above.
0
 
8080_DiverCommented:
This is going to sound odd but try it anyway. ;-)
SELECT LOG.ACCESS_DATE, LOG.CLIENT_IP, LOG.QUERY_STRING, LOG.CLIENT_BROWSER
FROM ZZ_ACCESS_LOG as LOG
WHERE REPLACE(LOG.CLIENT_BROWSER, ';','|') NOT LIKE '*bot*'

Open in new window

0
 
DataCuratorAuthor Commented:
Is that REPLACE function going to permanently alter my data?
0
 
8080_DiverCommented:
Nope, it is just going to alter it in the context of the WHERE clause . . . kind of like converting a number to a string in the where clause for a comparison purpose.
0
 
DataCuratorAuthor Commented:
I copied into another table to try the function without worrying about changing any data permanently. It's still not working. Same results as before. Should I be able to see all of the semicolons replaces with pipes? They are still showing up as semicolons in my results.
0
 
DataCuratorAuthor Commented:
Okay, this is truly strange. I went ahead and tried your exact query on the real table, and it now gives me an error message: type mismatch in expression.

Why?
0
 
8080_DiverCommented:
Since the data is not being permanently changed, the semicolons will still show up as semicolons.  
Just for test purposes, add a new column that shows the replace results.

SELECT LOG.ACCESS_DATE, LOG.CLIENT_IP, LOG.QUERY_STRING, LOG.CLIENT_BROWSER, REPLACE(LOG.CLIENT_BROWSER, ';','|') AS For_Test_Only
FROM ZZ_ACCESS_LOG as LOG
WHERE REPLACE(LOG.CLIENT_BROWSER, ';','|') NOT LIKE '*bot*'

Open in new window

0
 
DataCuratorAuthor Commented:
I fiddled a bit, and it seems it doesn't like that REPLACE statement. It gives me an #ERROR in that column.
0
 
8080_DiverCommented:
Okay, this is truly strange. I went ahead and tried your exact query on the real table, and it now gives me an error message: type mismatch in expression.

Why?
The Why? is because it thinks something doesn't match.
Where does the cursor wind up when it gives you the error?
0
 
8080_DiverCommented:
I was kind of afraid of that . . . I am stuck with Access 2007 and it's a little hard to test for earlier versions.  (It works great in Access 2007, if that's any consolation but then Access 2007 is handling the LIKE '*bot*' without the replace statement, too. ;-)
Okay, so the Acess fron end is not liking the data . . . what about setting up a stored procedure on SQL Server that lets you pass it a parameter that it uses to constrain the data and then returns the results?

CREATE PROCEDURE usp_Constrained_ClientBrowser_Selection
     @Constraint_Value    VarChar(10)
AS
BEGIN
 
    SET NOCOUNT ON;
 
    SELECT LOG.ACCESS_DATE, 
           LOG.CLIENT_IP, 
           LOG.QUERY_STRING, 
           LOG.CLIENT_BROWSER
    FROM ZZ_ACCESS_LOG as LOG
    WHERE REPLACE(LOG.CLIENT_BROWSER, ';','|') NOT LIKE '%' + @Constraint_Value + '%';
 
END

Open in new window

0
 
DataCuratorAuthor Commented:
I've been fiddling a bit. REPLACE is actually working okay (I needed to scroll down a bit to see it wasn't all error messages), but gives me #ERROR if the field is null. I think it may be these errors that are causing the type mismatch, but I can't seem to get rid of them all. I've tried IS NOT NULL and  <>"", but it still gives me blank fields. If I try to sort them, they do not aggregate. The computer seems to think there is something in there, but I have no idea what it could be.

I have never created a procedure in SQL Server, and I can't make changes to the Server side without going through through the Server admin. I suppose I will take it up with them, unless you have additional suggestions?
0
 
8080_DiverCommented:
Since the REPLACE is working, all we need to do is handlte the NULLs.
If it is the LOG.CLIENT_BROWSER column, you might be able to use the following bit of SQL.  If the WHERE clause will short-stop based upon the first partof the clause being true, then it shouldn't get an error on the second part.  (There are a couple of other options that could be tried if this doesn't work. ;-)

SELECT LOG.ACCESS_DATE, LOG.CLIENT_IP, LOG.QUERY_STRING, LOG.CLIENT_BROWSER, REPLACE(LOG.CLIENT_BROWSER, ';','|') AS For_Test_Only
FROM ZZ_ACCESS_LOG as LOG
WHERE LOG.CLIENT_BROWSER IS NULL
   OR REPLACE(LOG.CLIENT_BROWSER, ';','|') NOT LIKE '*bot*'

Open in new window

0
 
DataCuratorAuthor Commented:
Still no good. However, I've decided I am going to do this by IP address, which is probably what I should have done in the first place. I am going to award you points for all the time you have put in. Thanks for your help!
0
 
DataCuratorAuthor Commented:
Thanks for your help!
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 9
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now