Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

TSQL Labs 13 - Matching a delimited string against another delimited string

Sep 25 2008 7:57AM by Jacob Sebastian   

We have seen a number of posts on various string operations using XQuery functions and FOR XML operator. This post explains how to match a delimited string against another delimited string.

I wanted to share a piece of code that I wrote to help a member of our User Group. I found it to be very interesting and thought of sharing with all of you. One of the reasons why I wanted to share this example is because it uses an XML approach to solve the given problem.

Let us get into the details of the problem. Here is the data from a table that we are going to query.

id          Colors
----------- --------------------
1           Red                 
2           Red,Green           
3           Green, Yellow       
4           Yellow,Red,White    
5           Green, White        
6           White 

We need to write a stored procedure that receives a comma delimited string that contains one or more color names. For example, if we receive "Red,Yellow", we need to return every row that has "Red" or "Yellow" in the "Colors" column. (All rows except 5 and 6 in this example)

I suppose there are a number of ways to write this query. One of the options is to split the incoming parameter using your favorite method (and I decided to use XML). Once we have split the parameter, we can find the appropriate values using PATINDEX().

Here is the code that performs this.

SET NOCOUNT ON;

DECLARE @t TABLE (id INT, Colors VARCHAR(20))
INSERT INTO @t (id, Colors) SELECT 1, 'Red'
INSERT INTO @t (id, Colors) SELECT 2, 'Red,Green'
INSERT INTO @t (id, Colors) SELECT 3, 'Green, Yellow'
INSERT INTO @t (id, Colors) SELECT 4, 'Yellow,Red,White'
INSERT INTO @t (id, Colors) SELECT 5, 'Green, White'
INSERT INTO @t (id, Colors) SELECT 6, 'White'

DECLARE @Filter VARCHAR(100)
SET @Filter = 'Red,Yellow'

DECLARE @xmlFilter XML
SELECT @xmlFilter = CAST('' + REPLACE(@Filter, ',', '') + '' AS XML)

SELECT DISTINCT
    id,
    Colors
FROM @t t
CROSS JOIN (
    SELECT 
        x.i.value('.', 'VARCHAR(10)') AS filter
    FROM @XmlFilter.nodes('//i') x(i)
) b
WHERE PATINDEX('%' + b.filter + '%', Colors) > 0

/*
id          Colors
----------- --------------------
3           Green, Yellow       
1           Red                 
2           Red,Green           
4           Yellow,Red,White    
*/

I have not analyzed the performance factors. The CROSS JOIN might be a little expensive. PATINDEX() is found to be performing well, in many of the tests I did in the past. In fact, I found it to be performing better than LIKE, in many cases.

Let us see how this query works.

The first part of the query converts the comma delimited string to an XML document.

SELECT @xmlFilter = CAST('' + REPLACE(@Filter, ',', '') + '' AS XML)
/*
Red
Yellow
*/

Then the inner query retrieves the values from the XML document.

SELECT 
    x.i.value('.', 'VARCHAR(10)') AS filter
FROM @XmlFilter.nodes('//i') x(i)
/*
filter
----------
Red       
Yellow    
*/

The CROSS JOIN generates the following.

id          Colors
----------- --------------------
1           Red                 
1           Red                 
2           Red,Green           
2           Red,Green           
3           Green, Yellow       
3           Green, Yellow       
4           Yellow,Red,White    
4           Yellow,Red,White    
5           Green, White        
5           Green, White        
6           White               
6           White

The WHERE filter then identifies the rows that has one of the values present in the XML result.

id          Colors
----------- --------------------
1           Red                 
2           Red,Green           
3           Green, Yellow       
4           Yellow,Red,White    
4           Yellow,Red,White

Finally, the DISTINCT clause removes duplicate rows generated by the CROSS JOIN and we get the final result.

id          Colors
----------- --------------------
3           Green, Yellow       
1           Red                 
2           Red,Green           
4           Yellow,Red,White

Tags: TSQL, XQuery Lab, XQuery Functions, XML Workshop, TSQL Labs, TSQL Functions, String manipulation with XML Functions,


Jacob Sebastian
1 · 100% · 32220
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]