Yes.... I had tried that, but I was searching on a field containing data/time data, but the datatype it was set to was Varchar (30) .. which caused the search to bomb.
This brings up a datatype question... I'm searching here on field "URL" and field "time" and want to create an index (clustered) for faster searching, but I think I need to minimize the size of the URL field to enable more than one index if possible. What type and sized datatype would be best to contain URL data and allow efficient searching, while perhaps leaving enough space to create an index on the date field "TIME"? I'm thinking Varchar (1024) is bigger than it needs to be, but also wonder if "TEXT" would allow for any decent searching... ?
My goal is to be able to search on the time field (datatype "datetime") and the URL field (current datatype Varchar 1024).
Jack Corbett (1/3/2011)
Just add that to the Where Clause.
SELECTCHARINDEX(RTRIM(B.Words), U.url),
*
FROM
#urltbl U
CROSS JOIN #Badwords AS B
WHERE
MSGDATE >= '12/10/2010' AND
/* using the next day because if you want all of 1/1 and there are any times
included you need to do less than the next day */
MSGDATE < '1/2/2011' AND
CHARINDEX(RTRIM(B.Words), U.url) <> 0
ORDER BY
username,
url