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-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 476
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
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4269 Visits: 18732
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
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34224 Visits: 11359
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
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34224 Visits: 11359
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
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34224 Visits: 11359
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
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23033 Visits: 13362
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
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34224 Visits: 11359
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
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4772 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