Quick Tip Working With Kentico's Multiple Select Control

Kentico makes it easy to select from a list of options, but by default your options are stored as a pipe delimited list inside the database. There is no built in SQL method to use this list, but it’s not hard to separate out that list with a custom function inside the database, and use those results to join your related data.

Running the following SQL query will add a function to your database which splits strings on a specified delimiter. (note: I got this from Stack Overflow)

CREATE FUNCTION [dbo].[SplitStrings] 
( 
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN (SELECT Number = ROW_NUMBER() OVER (ORDER BY Number),
            Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number, 
        CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
        FROM sys.all_objects AS s1 CROSS APPLY sys.all_objects) AS n(Number)
    WHERE Number <= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
    ) AS y);

GO

Using this short SQL script below, you can use the results of that function to join on a table.

SELECT ##TOPN ##COLUMNS##
FROM [Source_Table] S
CROSS APPLY dbo.SplitStrings(S.MultiSelectColumn, '|') AS X
INNER JOIN [Joined_Table] C ON X.Item = c.MultiSelectValue

Written on July 4th, 2014 by Dan Walker
Tags: kentico, sql.

Want to read more?

If you’ve enjoyed what you’ve seen so far, you might like some of my other posts. Visit the archive to see all of my past writings.

Blog Archive »
 

About the Author

Dan Walker is a programmer from Grand Rapids, MI. He works at Gordon Food Service.

More About Dan »

Get in touch