July 4th, 2014
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