Jack Corbett (12/29/2010)

---

Okay, it took me a few minutes, but here's code that works with your sample:

SELECT
    CHARINDEX(RTRIM(B.Words), U.url),
    *
FROM
    #urltbl U
    CROSS JOIN #Badwords AS B
WHERE
    CHARINDEX(RTRIM(B.Words), U.url) <> 0
ORDER BY
    username,
    url 

Key points are that I'm using a CROSS JOIN so that each URL is checked against each bad word. This means that if a URL has more than 1 bad word it will be returned once for each bad word it contains. The other is that in your example you are using nchar(25) for the bad words which means that each entry is being padded out to 25 characters (50 bytes) AND you padded the bad words with spaces in the example, so I had to do an RTRIM to remove the padding in order to find matches in the URL since the bad words in the URL are not likely to be followed by spaces.

Jack Corbett (1/3/2011)

---

Just add that to the Where Clause.

SELECT
    CHARINDEX(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