CHARINDEX with Text in Table

Question

I need to check if any one of a table of text values are in a string. Is there a way to do this with one select statement and charindex or is there anothedr way? What I'm doing is checking for bot strings in a given user agent and setting a value in a table based on the result. I have a table of bots but I can't use a single charindex to check the user agent. I don't want to hard code multiple charindexes as the list of bots changes.

Answer 1

You would be better off using something like Full-Text Search http://msdn.microsoft.com/en-us/library/ms142571.aspx

---

When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Twitter: @Mr_Wharty
MC ID: Microsoft Transcript

Answer 2

What I did was something like this

If( Select COUNT (*)

     FROM dbo.BotsTable bt

     WHERE UserAgent Like '%'+bt.BotString+'%') > 0

Set myVar = 0

Else

Set myVar = 1

I put this in a function and pass it a user agent and return myVar. The function returns in under a second for a bot table with about 360 bot strings. I think this will suffice for my purpose.

Answer 3

     WHERE UserAgent Like '%'+bt.BotString+'%') > 0

This is bad SQL syntax as it will cause an index scan which will eventually cause your query to take longer and longer to execute as more data is added to your table.

Some research material for you:

https://msmvps.com/blogs/robfarley/archive/2010/01/22/sargable-functions-in-sql-server.aspx

http://blog.wharton.com.au/2011/06/18/sargable-predicates/

It returns in less than a second as your current table data is small and there is very little load on your database.

---

When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Twitter: @Mr_Wharty
MC ID: Microsoft Transcript

Answer 4

I don't know which row (or rows) the user agent is going to match in my table. I don't know where in the user agent string a bot string will be found, if at all. If I knew where in the user agent a match could be found I could substring the user agent and get a table seek. As it is, I don't see a way to avoid a full table scan.