Wednesday, May 10, 2017

Quick little SQL trick: A Reverse Like

Ok, quick one here but a good one.

Background: This came up when dealing with SharePoint Content Enrichment Web Service (CEWS) output. CEWS is a hook into the SharePoint Search crawler that gets called for every item that is crawled. We are wanting to store this in a database for reporting purposes. CEWS gets a list of Managed Properties, one of which can be the Path to the item. The question was, how can we tie this back to the site? Well, in our case we have Managed Properties on the site (more on this later, I promise) and we keep a site list in a table. The site list has the URL for the site, and all items are going to have paths that extend this URL. So, we want to do a like comparison between the path sent in and a column in the site table.  Here is what we are doing:

select * from SiteTable where '<path sent in for CEWS>' like URL+'%' 

See, the path sent for the crawled item is going to contain the URL from the SiteTable.  Job done.

No comments:

Post a Comment