We have two tables - one is the _click table and the other is a catalog of ProductIDs and we'd like to find all the clicks from the previous day where the ProductID is in the LinkContent field.

The problem is the ProductID data is not an exact match, but is like the value in LinkContent and so I don't know how to add the wildcards to find the matches.

LinkContent = http://www.somedomain.com/1234?utm_campaign=...

Value in Table ProductID is 1234


You could probably do it using a LIKE statement concatting the wildcards before and after the column.

e.g. click.LinkContent LIKE '%/' + prod.ProductID + '?%'

So full sample (with dummy fields, etc.)

SELECT prod.ProductID
, yourOtherFields
FROM [ProductIDs] prod
INNER JOIN [_Click] click
ON click.LinkContent LIKE '%/' + prod.ProductID + '\?%'
WHERE click.EventDate >= DATEADD(day, -1, GETDATE())

That should get you all records in past 24 hours that has a productID (from ProductIDs DE) in between the / of the URL and the ? (note the \ in front to escape it as ? is a wildcard character for SQL, so needs to be escaped to be literal) Query Parameter marker. Which appears to be where the ProductID would be located in your URLs.

| improve this answer | |
  • 1
    please add this to my tab! I had to make a few changes (based on data type, I used a CAST) but overall this got me on the right path. THANK YOU – George Rosedale Nov 8 '19 at 17:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.