July 9, 2010 at 9:53 am
I have a table with a column of double delimited strings. I need to break it out into all possible combinations without repeats and without repeats from one outer group to another. For example:
CREATE TABLE #TmpTbl (TblID INT PRIMARY KEY, MultiDelimStr VARCHAR(255))
INSERT #TmpTbl ( TblID, MultiDelimStr )
SELECT 101, 'Orange,Apple;Orange,Grape,Banana;Apple,Kiwi,Grape'
UNION
SELECT 202, 'Grape,Apple;Orange,Grape,Banana;Peach,Kiwi,Grape'
UNION
SELECT 303, 'Orange;Peach,Grape,Banana;Apple,Kiwi;Tangerine,Grapefruit'
From this I need the following result set:
101Orange,Grape,Apple
101Orange,Grape,Kwi
101Orange,Banana,Apple
101Orange,Banana,Kiwi
101Orange,Banana,Grape
101Apple,Orange,Kiwi
101Apple,Orange,Grape
101Apple,Grape,Kiwi
101Apple,Banana,Kiwi
101Apple,Banana,Grape
202Grape,Orange,Peach
202Grape,Orange,Kiwi
202Grape,Banana,Peach
202Grape,Banana,Kiwi
202Apple,Orange,Peach
202Apple,Orange,Kiwi
202Apple,Orange,Grape
202Apple,Grape,Peach
202Apple,Grape,Kiwi
202Apple,Banana,Grape
202Apple,Banana,Peach
202Apple,Banana,Kiwi
303Orange,Peach,Apple,Tangerine
303Orange,Peach,Apple,Grapefruit
303Orange,Peach,Kiwi,Tangerine
303Orange,Peach,Kiwi,Grapefruit
303Orange,Grape,Apple,Tangerine
303Orange,Grape,Apple,Grapefruit
303Orange,Grape,Kiwi,Tangerine
303Orange,Grape,Kiwi,Grapefruit
303Orange,Banana,Apple,Tangerine
303Orange,Banana,Apple,Grapefruit
303Orange,Banana,Kiwi,Tangerine
303Orange,Banana,Kiwi,Grapefruit
But the following row would be incorrect:
101Apple,Orange,Apple
Please, also note that I can count on at least 1 semicolon or up to 4, but no more than 4 per record. I don’t want to separate the rows with 1 semicolon and process them before moving on to the rows with 2 semicolons, etc.
I thought a double parse routine would get me close to where I want to be, but I can’t figure out how to put it back together again in the order my requirements desire. To break it out I have used:
SELECT FruitID, MultiDelimStr
,RANK() OVER(PARTITION BY FruitID ORDER BY N) AS GroupID
,SUBSTRING(';'+MultiDelimStr+';',N+1,CHARINDEX(';',';'+MultiDelimStr+';',N+1)-N-1) AS GroupFruit
INTO #FirstParse
FROM #FruitTbl F
CROSS JOIN dbo.Tally T
Where T.N < LEN(';'+MultiDelimStr+';')
AND SUBSTRING(';'+MultiDelimStr+';',N,1) = ';'
--SELECT * FROM #FirstParse
SELECT FruitID, GroupID
,SUBSTRING(','+GroupFruit+',',N+1,CHARINDEX(',',','+GroupFruit+',',N+1)-N-1) AS SoloFruit
INTO #SecondParse
FROM #FirstParse F
CROSS JOIN dbo.Tally T
Where T.N < LEN(','+GroupFruit+',')
AND SUBSTRING(','+GroupFruit+',',N,1) = ','
SELECT * FROM #SecondParse
To get the result set (just showing 101 because this is already long enough):
FruitIDGroupIDSoloFruit
1011 Orange
1011 Apple
1012 Orange
1012 Grape
1012 Banana
1013 Apple
1013 Kiwi
1013 Grape
Now how do I put it back together and achieve the desired result set without multiple IF statements and multiple sets of subqueries or a loop (cursor/while)? Or is there a better way than splitting it twice? I know I am missing something that I fear I have read before, but I just can't remember it :crazy: XML?
Thank you very much for your time and advice in advance.:-)
July 9, 2010 at 11:50 am
I'm not sure I understand all your requirements.
Why ID = 101 has 3 combinations? There are 4 fruits there...
Anyway this is what I could come up with. It could be a starter.
IF OBJECT_ID('Tempdb..#TmpTbl') IS NOT NULL DROP TABLE #TmpTbl
CREATE TABLE #TmpTbl (TblID INT PRIMARY KEY, MultiDelimStr VARCHAR(255))
INSERT #TmpTbl ( TblID, MultiDelimStr )
SELECT 101, 'Orange,Apple;Orange,Grape,Banana;Apple,Kiwi,Grape'
UNION
SELECT 202, 'Grape,Apple;Orange,Grape,Banana;Peach,Kiwi,Grape'
UNION
SELECT 303, 'Orange;Peach,Grape,Banana;Apple,Kiwi;Tangerine,Grapefruit'
;WITH Pass1 AS (
SELECT TblId, C.Value
FROM #TmpTbl AS A
CROSS APPLY dbo.fSplit(MultiDelimStr, ';') AS B
CROSS APPLY dbo.fSplit(B.Value, ',') AS C
),
Pass2 AS (
SELECT *
FROM (
SELECT DISTINCT *
FROM Pass1
) AS A
),
Ids AS (
SELECT DISTINCT TblId
FROM #TmpTbl
)
SELECT A.TblId, B.*
FROM Pass2 AS A
CROSS APPLY (
SELECT CStr = A.Value + (
SELECT ',' + Value AS [text()]
FROM Pass2 AS P1
WHERE P1.TblId = A.TblId
AND P1.Value <> A.Value
ORDER BY Value
FOR XML PATH('')
)
)AS B
This code uses a split function by Jeff Moden that can be found here: http://www.sqlservercentral.com/articles/T-SQL/62867/
I'm including the function here:
CREATE FUNCTION [dbo].[fSplit]
(
@Parameter VARCHAR(8000),
@SplitOn char(1)
)
RETURNS @Elements TABLE
(
ID INT identity(1,1),
Value VARCHAR(8000)
)
AS
BEGIN
--===== Add start and end commas to the Parameter so we can handle
-- single elements
SET @Parameter = @splitOn + @Parameter + @SplitOn
--===== Join the Tally table to the string at the character level and
-- when we find a comma, insert what's between that command and
-- the next comma into the Elements table
INSERT INTO @Elements (Value)
SELECT SUBSTRING(@Parameter,N+1,CHARINDEX(@splitOn,@Parameter,N+1)-N-1)
FROM dbo.Tally
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter,N,1) = @splitOn --Notice how we find the comma
RETURN
END
I'm sure this is not what you're after...
Good luck anyway
-- Gianluca Sartori
July 9, 2010 at 11:51 am
Don't forget to create the Tally table from the article code if you're using the split function!
Good luck with your query!
-- Gianluca Sartori
July 9, 2010 at 1:13 pm
I'm not sure I understand all your requirements.
Why ID = 101 has 3 combinations? There are 4 fruits there...
I am not sure I understand your question. ID 101 should have 10 combinations.
Group1 has 2fruits. Group2 has 3 fruits. Group3 has 3 fruits. Group1 shares 1 fruit with Group2 and 1 different fruit with Group3. Group2 shares 1 fruit with Group3. Since Apple, Orange, Apple is not allowed, you can't count the number of items per group and the number of groups and get your total number of combinations using simple math. You have to know the repeats. This leaves the 10 combinations I list above in the original post.
Thank you very much for the function and CTEs, I knew that I had seen something like that before. I will test against what I have and let you know if it does indeed answer my question.
July 9, 2010 at 1:43 pm
Gianluca,
Your solution, while helpful, does not return the desired result set.
I think I see the confusion in my explanation of the problem.
The problem is to come up with all of the unique GROUP combinations.
The result set must have one fruit from Group1 and one fruit from Group2 ... 1 fruit from GroupN.
So if the string field has 1 ';' then there are two groups; Group1 and Group2 (left and right of the ';'). The output should be one fruit from Group1 plus a ',' plus one fruit from Group2. More specifically, all the possible single-fruit-per-group combinations that keep Group1 fruits on the left and Group2 fruits on the right. With no fruit simultaneously in Group1 and Group2 spots during output.
So if the string field has 2 ';' then there are three groups; Group1, Group2, Group3. Group1's fruits are to the left of the first ';' and Group3's are to the right of the last ';' and Group2's are in the the middle. Then re-concatenate them such that all the possible single-fruit-per-group combinations that keep Group1 fruits on the left and Group2 fruits in the middle and Group3 fruits on the right. With no fruit simultaneously in Group1, Group2 and/or Group3 spots during output.
Etc.
In my original post I list out all the valid combinations for output. That is the format that is desired. I hope this helps clear things up and expedites aid.
Thank you all again for taking a look at this.
July 12, 2010 at 10:46 am
I must say I'm quite disappointed I could not solve this problem in a simple way.
Maybe It's something beyond my skills.
Anyway this is the best I could put together:
IF OBJECT_ID('Tempdb..#TmpTbl') IS NOT NULL DROP TABLE #TmpTbl
CREATE TABLE #TmpTbl (TblID INT PRIMARY KEY, MultiDelimStr VARCHAR(255))
INSERT #TmpTbl ( TblID, MultiDelimStr )
SELECT 101, 'Orange,Apple;Orange,Grape,Banana;Apple,Kiwi,Grape'
UNION
SELECT 202, 'Grape,Apple;Orange,Grape,Banana;Peach,Kiwi,Grape'
UNION
SELECT 303, 'Orange;Peach,Grape,Banana;Apple,Kiwi;Tangerine,Grapefruit'
;WITH Splits AS (
SELECT TblId, GRP_ID = B.Id, C.Value AS Fruit, C.Id AS Fruit_id, NumGroups = MAX(B.Id) OVER(PARTITION BY TblId)
FROM #TmpTbl AS A
CROSS APPLY dbo.fSplit(MultiDelimStr, ';') AS B
CROSS APPLY dbo.fSplit(B.Value, ',') AS C
),
ThreeRows AS (
SELECT N
FROM Tally
WHERE N <= 3
),
FourRows AS (
SELECT N
FROM Tally
WHERE N <= 4
),
CrossThree AS (
SELECT A.N AS [1], B.N AS [2], C.N AS [3]
FROM ThreeRows AS A
CROSS JOIN ThreeRows AS B
CROSS JOIN ThreeRows AS C
),
CrossFour AS (
SELECT A.N AS [1], B.N AS [2], C.N AS [3], D.N AS [4]
FROM FourRows AS A
CROSS JOIN ThreeRows AS B
CROSS JOIN ThreeRows AS C
CROSS JOIN ThreeRows AS D
)
SELECT DISTINCT B.TblId, B.Fruit + ',' + C.Fruit + ',' + D.Fruit
FROM CrossThree AS A
INNER JOIN Splits AS B
ON B.GRP_ID = 1
AND B.Fruit_ID = [1]
INNER JOIN Splits AS C
ON C.GRP_ID = 2
AND C.TblId = B.TblId
AND C.Fruit_ID = [2]
AND C.Fruit <> B.Fruit
INNER JOIN Splits AS D
ON D.GRP_ID = 3
AND D.TblId = C.TblId
AND D.Fruit_ID = [3]
AND D.Fruit <> B.Fruit
AND D.Fruit <> C.Fruit
WHERE B.NumGroups = 3
UNION ALL
SELECT DISTINCT B.TblId, B.Fruit + ',' + C.Fruit + ',' + D.Fruit + ',' + E.Fruit
FROM CrossFour AS A
INNER JOIN Splits AS B
ON B.GRP_ID = 1
AND B.Fruit_ID = [1]
INNER JOIN Splits AS C
ON C.GRP_ID = 2
AND C.TblId = B.TblId
AND C.Fruit_ID = [2]
AND C.Fruit <> B.Fruit
INNER JOIN Splits AS D
ON D.GRP_ID = 3
AND D.TblId = C.TblId
AND D.Fruit_ID = [3]
AND D.Fruit <> B.Fruit
AND D.Fruit <> C.Fruit
INNER JOIN Splits AS E
ON E.GRP_ID = 4
AND E.TblId = D.TblId
AND E.Fruit_ID = [4]
AND E.Fruit <> B.Fruit
AND E.Fruit <> C.Fruit
AND E.Fruit <> D.Fruit
WHERE B.NumGroups = 4
Basically the code builds two work tables (CrossThree and CrossFour) with "N" columns, holding all N^N possible combinations of values between 1 and "N".
Once the work tables are in place, I join them back to the splitted strings, choosing the right worktable looking at the number of groups.
The main thing I don't like about this code is that it relies on fixed numbers of groups, so that it should be generated dynamically after a first pass on the input data to gather the minimum/maximum number of groups.
I think this problem could be solved much better with a CLR aggregate, but it's a technique I don't master.
I hope someone can help with something better than this.
Gianluca
-- Gianluca Sartori
July 12, 2010 at 11:12 am
Your solution is similar to mine with more joins than I believe to be efficient, but still effective.
I am also hoping someone else can provide a cleaner, simpler, more efficient way to solve this challenge.
I do appreciate the time you put into this; thanks! 😀
July 12, 2010 at 11:36 am
I was wondering if you had control over the schema and could change it to have all the data broken out into different tables? Gianluca did that with his CTE, and with that something like this works:
;WITH Splits AS (
SELECT TblId, GRP_ID = B.Id, C.Value AS Fruit
FROM #TmpTbl AS A
CROSS APPLY dbo.fSplit(MultiDelimStr, ';') AS B
CROSS APPLY dbo.fSplit(B.Value, ',') AS C
)
SELECT one.tblID, one.Fruit + ',' + two.Fruit + ISNULL(',' + three.Fruit, '') + ISNULL(',' + four.Fruit, '') + ISNULL(',' + five.Fruit, '')
FROM Splits one
LEFT JOIN Splits two on (one.tblID = two.tblID AND two.GRP_ID = 2)
LEFT JOIN Splits three on (one.tblID = three.tblID AND three.GRP_ID = 3)
LEFT JOIN Splits four on (one.tblID = four.tblID AND four.GRP_ID = 4)
LEFT JOIN Splits five on (one.tblID = five.tblID AND five.GRP_ID = 5)
WHERE ISNULL(one.Fruit,'') != ISNULL(two.Fruit,'*')
and ISNULL(one.Fruit,'') != ISNULL(three.Fruit,'*')
and ISNULL(one.Fruit,'') != ISNULL(four.Fruit,'*')
and ISNULL(one.Fruit,'') != ISNULL(five.Fruit,'*')
and ISNULL(two.Fruit,'') != ISNULL(three.Fruit,'*')
and ISNULL(two.Fruit,'') != ISNULL(four.Fruit,'*')
and ISNULL(two.Fruit,'') != ISNULL(five.Fruit,'*')
and ISNULL(three.Fruit,'') != ISNULL(four.Fruit,'*')
and ISNULL(three.Fruit,'') != ISNULL(five.Fruit,'*')
and ISNULL(four.Fruit,'') != ISNULL(five.Fruit,'*')
AND one.GRP_ID = 1
It's basically just a table join making sure you don't get duplicates back. Ugly, but it worked with the sample data.
Chad
July 12, 2010 at 12:58 pm
I was wondering if you had control over the schema and could change it to have all the data broken out into different tables?
Doesn't every DBA/D want control over that? LOL And no, I do not.
Ugly, but it worked with the sample data.
Yeah, but do you think it is possible to do this more "Swan" and less "Duckling?" Is there a better way to start? (other than changing the schema, of course;-))
What is the performance hit of joining the same table 5 times going to be when I try to do 100,000 of these at once?
Thank you for your time and help.
July 12, 2010 at 2:18 pm
Well... not any answers, but some more questions and thoughts that might spark a new idea:
When you query out for these, you are doing it based on the TBLID, right? You aren't looking for everything from everywhere each time? With that in mind, you could target those rows with a where clause in the CTE and that would make a huge difference with that column indexed. I wouldn't think that would be too bad at all.
What do the insert/updates against this table look like - are they frequent and constant? I hate recommending a trigger because of the overhead they represent, but if inserts, updates and deletes are infrequent, you could use a trigger to mirror the structure in 5 seperate tables and with TBLID indexed the performance shouldn't be too bad. Or, you could just prebuild all the information on the fly from the trigger in the first place.
I know you don't have control over the schema, but if you could take some metrics to someone (I'm just making this up, but "If we change the schema we'll go from 5 minute queries to sub-second queries" or whatever it turns out to be), would they consider making some changes? If you use some sample data and an environment similar to what you have in production, can you see what difference a new schema might make?
Do you have enough control over the schema to change the content of the string? I'm not the best at XML, but if the string was in an XML format, there might (I don't know) be some new opportunities that don't involve all the joins and loops and just leverage the XML.
What is the consuming target for the data? Is it a report, or is this an input to another algorithm? SQL doesn't have a full complement of string tools, would it be faster to offload some of the work to the next layer?
Do you need real-time access to the data? If you prebuilt a report table on a semi-regular basis so that all the crunching was done once and all the reporting/consuming was done off a slightly out-of-date copy, would it be satisfactory?
And I guess the other question is do you have an environment to test a real-sized load to see what it would do?
Thanks,
Chad
July 12, 2010 at 2:37 pm
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.
July 12, 2010 at 2:47 pm
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
July 14, 2010 at 6:29 am
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
SQLkiwi blog
@SQL_Kiwi
July 14, 2010 at 6:32 am
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.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
SQLkiwi blog
@SQL_Kiwi
July 14, 2010 at 6:35 am
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
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply