SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Double Delimited String Parsing and Re-Concatenation Help


Double Delimited String Parsing and Re-Concatenation Help

Author
Message
pmcpherson
pmcpherson
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 462
Thank you Chad. I just got pulled in another direction while reading your reply. It might be a while before I can answer all those questions.
Chad Crawford
 Chad Crawford
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2880 Visits: 18718
Oh no - don't answer all the questions. Just take a look and see if anything jumps out at you. I'm thinking the first one might be the best - if you are only ever querying for specific TBLIDs, indexing that and adding it to the where will probably make just about anything fast enough to be ok... well, depending on your requirements. The thoughts I wrote down got progressively worse as I kept thinking, but they were ideas that might work, depending on the situation you are in, and since I thought of them. I felt compelled to write them down. That doesn't mean you are compelled to listen to them :-P

Thanks,
Chad
Paul White
Paul White
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16326 Visits: 11355
Gianluca Sartori (7/12/2010)
I think this problem could be solved much better with a CLR aggregate, but it's a technique I don't master.

A CLR aggregate wouldn't be the right choice because an aggregate only returns one row from multiple rows of input. What we need to do here is return multiple rows (the combinations) from a single row input (the multi-delimited string). For that, we need a streaming CLR table-valued function.

Using the table and sample data kindly provided by pmcpherson, the full solution becomes:


SELECT TT.TblID, GC.combination
FROM #TmpTbl TT
CROSS
APPLY dbo.GetCombinations(TT.MultiDelimStr) GC;



It is extremely fast and quite neat. The source code and binary will follow in my next post.

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16326 Visits: 11355
C# source code, for those that prefer to compile for themselves:


using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
[SqlFunction
(
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.None,
FillRowMethodName = "FillRow",
IsDeterministic = true,
IsPrecise = true
)
]
public static IEnumerable GetCombinations
(
[SqlFacet(MaxSize = 256, IsNullable = false, IsFixedLength = false)] SqlString input
)
{
// Check for a NULL parameter
if (input.IsNull)
{
return new string[0];
}

// Create a list of string arrays to hold each member of each group
List<string[]> groupList = new List<string[]>();

// Split the input into groups on the semicolon
string[] groups = input.Value.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);

// For each group, add each item of the group to a string array in the list
for (int i = 0; i < groups.Length; i++)
{
groupList.Add(groups[i].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries));
}

// Do the recursive magic (returns a List of strings, each of which is a combination)
return recursiveAppend(0, groupList);
}

// Called by SQL Server for each member of the List of strings
public static void FillRow(object obj, out SqlString combination)
{
// Remove the final comma and return the combination string as a new row
string output = (string)obj;
output = output.Substring(0, output.Length - 1);
combination = new SqlString(output);
}

// The magic
static List<string> recursiveAppend(int level, List<string[]> inputList)
{
List<string> toReturn = new List<string>();

if (level == inputList.Count)
{
toReturn.Add(String.Empty);
return toReturn;
}

foreach (string item in inputList[level])
{
foreach (string partialList in recursiveAppend(level + 1, inputList))
{
if (!partialList.Contains(item + ','))
{
toReturn.Add(item + "," + partialList);
}
}
}
return toReturn;
}
}





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16326 Visits: 11355
T-SQL assembly and function creation:

Assembly:

CREATE ASSEMBLY [StringCombo] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300E5AA3D4C0000000000000000E00002210B010800000E000000060000000000009E2C0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000482C000053000000004000001803000000000000000000000000000000000000006000000C000000A42B00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000A40C000000200000000E000000020000000000000000000000000000200000602E7273726300000018030000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000802C0000000000004800000002000500B0210000F409000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001330050066000000010000110F00281300000A2C07168D180000012A731400000A0A0F00281500000A178D190000010D09161F3B9D09176F1600000A0B160C2B230607089A178D1900000113041104161F2C9D1104176F1600000A6F1700000A0817580C08078E6932D7160628030000062A00001330040024000000020000110274180000010A0616066F1800000A17596F1900000A0A0306731A00000A81030000012A1B3004009700000003000011731B00000A0A02036F1C00000A330D067E1D00000A6F1E00000A062A03026F1F00000A0D1613042B650911049A0B0217580328030000066F2000000A13052B2F1205282100000A0C08071F2C8C19000001282200000A6F2300000A2D120607720100007008282400000A6F1E00000A1205282500000A2DC8DE0E1205FE160300001B6F2600000ADC1104175813041104098E693294062A000110000002003E003C7A000E000000001E02282700000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000F8020000237E0000640300001004000023537472696E6773000000007407000008000000235553007C0700001000000023475549440000008C0700006802000023426C6F620000000000000002000001471502080900000000FA253300160000010000001C000000020000000400000005000000270000000F0000000300000003000000010000000200000000000A0001000000000006003F0038000600590046000A00860071000600C300A8000A000101E600060042012301060071015F01060088015F010600A5015F010600C4015F010600DD015F010600F6015F01060011025F0106002C025F01060045022301060059022301060067025F01060080025F010600B0029D024F00C40200000600F302D30206001303D3020A003D03E60006005D03380006006E0338000600730338001300BE0300000600FC0338000000000001000000000001000100010010001A000000050001000100502000000000960090000A000100C420000000009600A00011000200F420000000009100CA0019000400A821000000008618DA002800060000000100E000000001001301020002001701000001004F010000020055012900DA0028003100DA0028003900DA005E004100DA005E004900DA005E005100DA005E005900DA005E006100DA005E006900DA005E007100DA005E007900DA0063008100DA005E008900DA005E009100DA005E009900DA006800A900DA006E00B100DA002800B900DA002800190052034D010C00DA002800190064035801C10086035C010C008C036501C10090037B01C1009B037F011900DA005E001400DA0028000C00A5037B01C100AF038F0114008C0365010C00B50392011400C90398011C00D703A701C100E303AC01C100EA03B201C100E303B7011C00F3034D01E100080428000900DA00280020009300730024000B002C002E004300E7012E002300E1012E002B00E1012E003300E1012E003B00D0012E001B00D0012E004B00E1012E005B00E1012E006300FF012E00730029022E007B0036022E0083003F022E008B0048026B018501BE0151018901A1010480000001000000000000000000000000003103000002000000000000000000000001002F000000000002000000000000000000000001006500000000000000003C4D6F64756C653E00537472696E67436F6D626F2E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E436F6C6C656374696F6E730049456E756D657261626C650053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E6700476574436F6D62696E6174696F6E730046696C6C526F770053797374656D2E436F6C6C656374696F6E732E47656E65726963004C697374603100726563757273697665417070656E64002E63746F7200696E707574004D6963726F736F66742E53716C5365727665722E5365727665720053716C4661636574417474726962757465006F626A00636F6D62696E6174696F6E0053797374656D2E52756E74696D652E496E7465726F705365727669636573004F7574417474726962757465006C6576656C00696E7075744C6973740053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500537472696E67436F6D626F0053716C46756E6374696F6E417474726962757465006765745F49734E756C6C00537472696E67006765745F56616C7565004368617200537472696E6753706C69744F7074696F6E730053706C697400416464006765745F4C656E67746800537562737472696E67006765745F436F756E7400456D707479006765745F4974656D00456E756D657261746F7200476574456E756D657261746F72006765745F43757272656E7400436F6E63617400436F6E7461696E73004D6F76654E6578740049446973706F7361626C6500446973706F73650000032C0000000000412656320686FE4198E338DDF3A796A60008B77A5C561934E0890600011209110D070002011C10110D0E0002151211010E08151211011D0E0320000131010003005408074D617853697A650001000054020A49734E756C6C61626C650054020D497346697865644C656E67746800042001010E0420010102052001011151042001010880D8010003005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A4461746141636365737300000000540E1146696C6C526F774D6574686F644E616D650746696C6C526F77540E0F5461626C65446566696E6974696F6E1A636F6D62696E6174696F6E204E564152434841522834303030290320000206151211011D0E0320000E0820021D0E1D0311690520010113000F0705151211011D0E1D0E081D031D03032000080520020E08080307010E05151211010E02060E05200113000808200015116D0113000515116D010E04200013000500020E1C1C042001020E0600030E0E0E0E110706151211010E0E0E1D0E0815116D010E1001000B537472696E67436F6D626F000005010000000017010012436F7079726967687420C2A920203230313000002901002430303264663264382D663839392D343162392D386632632D36616335653161613832373400000C010007312E302E302E3000000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000000000E5AA3D4C000000000200000088000000C02B0000C00D0000525344537BB1CA208BBDE24C8EC97D6E38B8E62203000000433A5C446F63756D656E747320616E642053657474696E67735C5061756C5C6D7920646F63756D656E74735C76697375616C2073747564696F20323031305C50726F6A656374735C537472696E67436F6D626F5C6F626A5C52656C656173655C537472696E67436F6D626F2E70646200702C000000000000000000008E2C0000002000000000000000000000000000000000000000000000802C000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000C00200000000000000000000C00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00420020000010053007400720069006E006700460069006C00650049006E0066006F000000FC010000010030003000300030003000340062003000000040000C000100460069006C0065004400650073006300720069007000740069006F006E000000000053007400720069006E00670043006F006D0062006F000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000040001000010049006E007400650072006E0061006C004E0061006D006500000053007400720069006E00670043006F006D0062006F002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100300000004800100001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000053007400720069006E00670043006F006D0062006F002E0064006C006C00000038000C000100500072006F0064007500630074004E0061006D0065000000000053007400720069006E00670043006F006D0062006F000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000A03C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;



Function:

CREATE FUNCTION [dbo].[GetCombinations]
(
@input NVARCHAR(256)
)
RETURNS TABLE
(
combination NVARCHAR(4000) NULL
)
WITH EXECUTE AS CALLER
AS EXTERNAL NAME [StringCombo].[UserDefinedFunctions].[GetCombinations];





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Gianluca Sartori
Gianluca Sartori
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10152 Visits: 13351
Great! I was hoping you could take a look at this thread.
I first thought of a CLR aggregate because I was thinking about the second part of the problem (aggregating the split tokens).
Very nicely done, Paul.

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Paul White
Paul White
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16326 Visits: 11355
Gianluca Sartori (7/14/2010)
I first thought of a CLR aggregate because I was thinking about the second part of the problem (aggregating the split tokens).

Oh right, I see. That makes sense to me now.

Luckily, the function can do the splitting and re-forming very efficiently per-row, so you don't get the huge overhead (memory-wise) of splitting the whole thing and then concatenating.

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
jcrawf02
jcrawf02
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2368 Visits: 19324
Chad Crawford (7/12/2010)
Oh no - don't answer all the questions. Just take a look and see if anything jumps out at you. I'm thinking the first one might be the best - if you are only ever querying for specific TBLIDs, indexing that and adding it to the where will probably make just about anything fast enough to be ok... well, depending on your requirements. The thoughts I wrote down got progressively worse as I kept thinking, but they were ideas that might work, depending on the situation you are in, and since I thought of them. I felt compelled to write them down. That doesn't mean you are compelled to listen to them :-P

Thanks,
Chad
phew...:-P

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search