Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Double Delimited String Parsing and Re-Concatenation Help Expand / Collapse
Author
Message
Posted Monday, July 12, 2010 2:37 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 8:11 AM
Points: 55, Visits: 406
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.
Post #951043
Posted Monday, July 12, 2010 2:47 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:19 AM
Points: 2,607, Visits: 17,927
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

Thanks,
Chad
Post #951046
Posted Wednesday, July 14, 2010 6:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 4:20 PM
Points: 11,194, Visits: 11,142
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #952258
Posted Wednesday, July 14, 2010 6:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 4:20 PM
Points: 11,194, Visits: 11,142
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #952260
Posted Wednesday, July 14, 2010 6:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 4:20 PM
Points: 11,194, Visits: 11,142
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #952262
Posted Wednesday, July 14, 2010 6:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:18 AM
Points: 5,018, Visits: 10,535
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #952268
Posted Wednesday, July 14, 2010 6:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 4:20 PM
Points: 11,194, Visits: 11,142
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #952285
Posted Wednesday, July 14, 2010 11:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 8:59 AM
Points: 2,670, Visits: 19,241
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

Thanks,
Chad
phew...


---------------------------------------------------------
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."
Post #952536
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse