SQL SERVER SOUL: Cross Apply - Multiple Keyword Search

SQL SERVER SOUL: Cross Apply - Multiple Keyword Search: Background I was told to write a procedure where user can input multiple words (comma delimiter) for search and wherever these wo...

Cross Apply - Multiple Keyword Search

Background

I was told to write a procedure where user can input multiple words (comma delimiter) for search and wherever these words are available in any row of the particular column I need to display those rows.

So written a simple T-SQL query using ‘Cross Apply’ and user defined ‘Split function’ to search all the words in particular column.

Requirement

A user can input multiple word or phrase separated by comma (Comma as delimiter) and result should be returning all rows wherever those input word / phrase reside in that particular column.

To make this understand better I’ll use an example and I’ll take you step by step on this. Firstly we will create a temporary table and will fill it with some values which could fulfill our requirement. Secondly we will declare input parameter and will assign input search string to the parameter. Thirdly we will write T-SQL using ‘Cross apply’ and user defined table valued ‘Split function’ to search all the input words assigned to input parameter.

Now about user defined table valued split function, this function will split the multiple words using delimiter into rows as part of one column. Here this function required two parameters

 -First as input string which needs to be broken into rows based on some delimiter 

 -Second the delimiter value like Comma or semicolon etc.

 Example:

CREATE TABLE #Comments

(Id INT

,Comment VARCHAR(MAX)

)

INSERT INTO #Comments Values

 (1,'Final level of this Stairway takes you through how to identify and fix common errors.')

,(2,'SQL Server 2000 no default server, database or application role was available to be able to execute all stored procedures.')

,(3,'An article discussing DDL Auditing. Learn how to track schema changes that occur in your database, enabling you to see who changed what and when.')

,(4,'Microsoft SQL Server 2012 Integration Services (SSIS) provides significant improvements in both the developer and administration experience. This article provides tips that can help to make the upgrade to Microsoft SQL Server 2012 Integration Services successful.')

,(5,'Thursday April 19th 12PM noon Central, Todd McDermid will discuss "SSIS components for Data Warehouse Dimensional data processing".')

So far we had created the temporary table and had inserted few values into this table. Now we will identify some words / phrase to search out from the above values and would see how our query will respond on the same. So let’s find out words “Final Level” and “SQL Server 2012” from the above table.

We will assign these words to input parameter using comma as delimiter. This would be as below.

DECLARE @SearchString VARCHAR(MAX) ='Final Level,SQL SERVER 2012'

Now the real magic of ‘Cross Apply’ begins. Cross Apply will help in finding words “Final Level” and “SQL Server 2012” in particular column of temporary table in every row. Here table valued split function will split this word 'Final level,SQL Server 2012' into rows and making them as individual value of “Final level” and “SQL Server 2012’ and Cross Apply will take each individual word and will search in temporary table and if the particular word is available in any row of the searched column it will get added to the result, this will happen for each individual word returned from table valued split function.

SELECT DISTINCT

       cm.Id

      ,cm.Comment

FROM #Comments cm

      CROSS Apply dbo.ufn_Split (@SearchString,',') src

WHERE

  Comment Like '%' + src.StrVal+ '%'

Result

As result two rows will return, where word “Final level” and “SQL Server 2012” are available, this would be for ID 1 and 4 from #Comments temporary table as shown below.

The above example will also help in finding words in same row, let’s say user want to find row where “Final Level” and “Common Error” words are there. So in this case user will input these words as comma separated and as result SQL server will return one row with ID 1 from #Comments table where both these words are available in same row as shown below. 

Conclusion

We can use “Cross Apply” to search multiple words in particular column of table. This will help us to avoid While loop or Cursor in our Procedure or T-SQL to find multiple words in same row or in a table.