September 2, 2010 at 4:53 am
Paul White NZ (9/2/2010)
Steve,I found a few spare minutes to slightly modify Adam's routine to work the way you want:
Great stuff Paul:-)
Just one minor problem that it doesn't return the last item if it's blank
Cheers
Steve
September 2, 2010 at 6:42 am
steve-893342 (9/2/2010)
Just one minor problem that it doesn't return the last item if it's blank.
Not sure I agree - a single trailing delimiter shouldn't return an extra blank record, should it?
-- Three records (last record not delimited)
SELECT *
FROM dbo.SplitString_Multi(N'A,B,C', N',');
-- Three records (last record delimited)
SELECT *
FROM dbo.SplitString_Multi(N'A,B,C,', N',');
-- Three plus blank
SELECT *
FROM dbo.SplitString_Multi(N'A,B,C,,', N',');
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 2, 2010 at 7:04 am
Paul White NZ (9/2/2010)
steve-893342 (9/2/2010)
Just one minor problem that it doesn't return the last item if it's blank.Not sure I agree - a single trailing delimiter shouldn't return an extra blank record, should it?
-- Three records (last record not delimited)
SELECT *
FROM dbo.SplitString_Multi(N'A,B,C', N',');
-- Three records (last record delimited)
SELECT *
FROM dbo.SplitString_Multi(N'A,B,C,', N',');
-- Three plus blank
SELECT *
FROM dbo.SplitString_Multi(N'A,B,C,,', N',');
This may illustrate the problem better
WITH cte (col) AS
(
SELECT 'A,B,C,D,E' UNION ALL
SELECT 'A,B,C,D,' UNION ALL
SELECT 'A,B,C,,E'
)
SELECT sequence, col, item
FROM cte
CROSS APPLY
SplitString_Multi(col, ',')
Returns 14 rows when it should return 15
September 2, 2010 at 8:13 am
steve-893342 (9/2/2010)
Returns 14 rows when it should return 15
Are you expecting the "A,B,C,D," to produce 5 records?
To me, the trailing comma is delimiting the 'D' and so should not result in an extra blank item.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 2, 2010 at 8:28 am
Paul White NZ (9/2/2010)
steve-893342 (9/2/2010)
Returns 14 rows when it should return 15Are you expecting the "A,B,C,D," to produce 5 records?
Yes precisely, the number of rows returned from the split is defined as (number of commas) + 1
In this way the cardinality is preserved following the split
September 2, 2010 at 9:46 am
steve-893342 (9/2/2010)
Yes precisely, the number of rows returned from the split is defined as (number of commas) + 1
Ok, let's go with that. Thanks for persisting 🙂
CREATE ASSEMBLY Utility
AUTHORIZATION dbo
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300B3C57F4C0000000000000000E00002210B010800000E00000006000000000000FE2C0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000A82C000053000000004000000803000000000000000000000000000000000000006000000C000000082C00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000040D000000200000000E000000020000000000000000000000000000200000602E7273726300000008030000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000E02C000000000000480000000200050054220000B40900000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000D2026F1300000A2D1A036F1300000A2D12026F1400000A036F1400000A73050000062A168D1A000001168D1A00000173050000062A000000133003002C0000000100001102A5030000020A0312007B01000004731500000A81050000010412007B02000004731600000A81060000012A1E02281700000A2A3E02037D0100000402047D020000042A03300300530000000000000002281700000A02037D0600000402038E697D0800000402047D0700000402048E69D27D0900000402027B0900000417FE017D0A00000402167D0500000402167D0300000402027B09000004155A7D040000042ABE027B05000004027B06000004027B03000004027B04000004027B0300000459731900000A73040000068C030000022A0013300300C90000000200001102257B0500000417587D05000004027B04000004027B080000043202162A02027B04000004027B09000004587D03000004027B030000040A2B65170B027B0A0000042C16027B060000040693027B0700000416932E39160B2B35160C2B28060858027B080000042F14027B0600000406085893027B0700000408932E04160B2B0E081758D20C08027B0900000432CF072C0902067D04000004172A0617580A06027B08000004329202027B04000004027B09000004587D0300000402027B080000047D04000004172A5A02167D0300000402027B09000004155A7D040000042A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000080030000237E0000EC0300002804000023537472696E6773000000001408000008000000235553001C0800001000000023475549440000002C0800008801000023426C6F6200000000000000020000015717A2010902000000FA253300160000010000001D000000040000000A00000008000000090000000100000019000000100000000200000001000000010000000100000001000000020000000200000000000A0001000000000006005D005600060064005600060081006E000A00AE0099000A00C90099000A00D20099000A008F0174010600CE01AF0106000502F30106001C02F30106003902F30106005802F30106007102F30106008A02F3010600A502F3010600C002F3010600D902AF010600ED02AF010600FB02F30106001403F3010600440331035700580300000600870367030600A70367030A00D10374010600FB03560006000004AF0106001604AF010600210456000000000001000000000001000100010010001A0000000500010001000B0110002F000000090001000400020010003C0000000500030005000600F00022000600F90025000100190122000100210122000100F0002200210029013E00210033013E0021003D0122002100470142002100540145005020000000009600B7000A0001008820000000009600DC0013000300C020000000008618EA001E000600C820000000008618EA0028000600D820000000008618EA002E000800372100000000E609FE0036000A00682100000000E6010A013A000A003D2200000000E60113011E000A00000001006E0100000200A10100000100AB0102000200F00002000300F90000000100DB0100000200E40100000100E90100000200A10104000D003900EA001E004100EA001E004900EA0072005100EA0072005900EA0072006100EA0072006900EA0072007100EA0072007900EA0072008100EA0072008900EA0077009100EA0072009900EA007200A100EA007200A900EA007C00B900EA008200C100EA001E00C900EA001E002100E6033A002100F103D4002900EA0082003100EA0072000900EA001E00D900EA00DE00E900EA00E40020009300870024000B004C002E003B00F2002E001B00F2002E002300FF002E002B00FF002E003300FF002E0063001D012E008B0066012E004B00FF002E005B00FF002E00430005012E00730047012E007B0054012E0083005D0144000B005F00D900EC0004000100000066014800020006000300048000000100000000000000000000000000C503000002000000000000000000000001004D000000000002000000000000000000000001008D000000000003000200040002000000003C4D6F64756C653E00537472696E675574696C732E646C6C0055736572446566696E656446756E6374696F6E73004F75747075745265636F72640053706C6974537472696E674D756C7469006D73636F726C69620053797374656D004F626A6563740056616C7565547970650053797374656D2E436F6C6C656374696F6E730049456E756D657261746F720053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730053706C6974537472696E675F4D756C74690053716C496E7433320053716C537472696E670046696C6C526F775F4D756C7469002E63746F720073657175656E6365006974656D006765745F43757272656E74004D6F76654E657874005265736574006C617374506F73006E657874506F7300746865537472696E670064656C696D6974657200737472696E674C656E0064656C696D697465724C656E00697353696E676C654368617244656C696D0043757272656E7400496E707574004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650044656C696D69746572006F626A0053797374656D2E52756E74696D652E496E7465726F705365727669636573004F75744174747269627574650053657175656E6365004974656D00546865537472696E670053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500537472696E675574696C730053716C46756E6374696F6E417474726962757465006765745F49734E756C6C006765745F56616C75650043686172005374727563744C61796F7574417474726962757465004C61796F75744B696E6400537472696E67000003200000000000C84BC32985DEE5489E1FE2D7A168EC610008B77A5C561934E089080002120D121112110A0003011C1011151011190320000102060802060E05200201080E072002011D031D030320001C0320000203061D030206050206020328001C12010001005408074D617853697A65FFFFFFFF12010001005408074D617853697A65FF000000042001010E042001010205200101115904200101084C01000200540E1146696C6C526F774D6574686F644E616D650D46696C6C526F775F4D756C7469540E0F5461626C65446566696E6974696F6E136974656D206E766172636861722834303030290420001D03040701110C052001011171072003011D0308080507030802050C0100075574696C697479000005010000000017010012436F7079726967687420C2A920203230313000002901002436616266323165302D393063382D343865392D626165362D36666636363966336562653200000C010007312E302E302E3000000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000000000B3C57F4C000000000200000084000000242C0000240E00005253445326D0FEDE5ECCC3408D78EA0B0EDDDEE90E000000433A5C446F63756D656E747320616E642053657474696E67735C5061756C5C4D7920446F63756D656E74735C56697375616C2053747564696F20323031305C50726F6A656374735C5574696C6974795C6F626A5C52656C656173655C537472696E675574696C732E70646200D02C00000000000000000000EE2C0000002000000000000000000000000000000000000000000000E02C000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000B00200000000000000000000B00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00410020000010053007400720069006E006700460069006C00650049006E0066006F000000EC0100000100300030003000300030003400620030000000380008000100460069006C0065004400650073006300720069007000740069006F006E00000000005500740069006C006900740079000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000040001000010049006E007400650072006E0061006C004E0061006D006500000053007400720069006E0067005500740069006C0073002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100300000004800100001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000053007400720069006E0067005500740069006C0073002E0064006C006C000000300008000100500072006F0064007500630074004E0061006D006500000000005500740069006C006900740079000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000003D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION dbo.SplitString_Multi
(
@Input NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
(
sequence INTEGER NULL,
item NVARCHAR (4000) NULL
)
WITH EXECUTE AS CALLER
AS EXTERNAL NAME Utility.UserDefinedFunctions.SplitString_Multi;
Source:
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(
FillRowMethodName = "FillRow_Multi",
TableDefinition = "sequence INTEGER, item NVARCHAR(4000)"
)
]
public static IEnumerator SplitString_Multi(
[SqlFacet(MaxSize = -1)]
SqlChars Input,
[SqlFacet(MaxSize = 255)]
SqlChars Delimiter
)
{
return (
(Input.IsNull || Delimiter.IsNull) ?
new SplitStringMulti(new char[0], new char[0]) :
new SplitStringMulti(Input.Value, Delimiter.Value));
}
private struct OutputRecord
{
public int sequence;
public string item;
public OutputRecord(int Sequence, string Item)
{
this.sequence = Sequence;
this.item = Item;
}
}
public static void FillRow_Multi(object obj, out SqlInt32 sequence, out SqlString item)
{
OutputRecord r = (OutputRecord)obj;
sequence = new SqlInt32(r.sequence);
item = new SqlString(r.item);
}
public class SplitStringMulti : IEnumerator
{
public SplitStringMulti(char[] TheString, char[] Delimiter)
{
theString = TheString;
stringLen = TheString.Length;
delimiter = Delimiter;
delimiterLen = (byte)(Delimiter.Length);
isSingleCharDelim = (delimiterLen == 1);
sequence = 0;
lastPos = 0;
nextPos = delimiterLen * -1;
}
#region IEnumerator Members
public object Current
{
get
{
return new OutputRecord(sequence, new string(theString, lastPos, nextPos - lastPos));
}
}
public bool MoveNext()
{
sequence++;
if (nextPos >= stringLen)
return false;
else
{
lastPos = nextPos + delimiterLen;
for (int i = lastPos; i < stringLen; i++)
{
bool matches = true;
if (isSingleCharDelim)
{
if (theString != delimiter[0])
matches = false;
}
else
{
for (byte j = 0; j < delimiterLen; j++)
{
if (((i + j) >= stringLen) || (theString != delimiter[j]))
{
matches = false;
break;
}
}
}
if (matches)
{
nextPos = i;
return true;
}
}
lastPos = nextPos + delimiterLen;
nextPos = stringLen;
return true;
}
}
public void Reset()
{
lastPos = 0;
nextPos = delimiterLen * -1;
}
#endregion
private int lastPos;
private int nextPos;
private int sequence;
private readonly char[] theString;
private readonly char[] delimiter;
private readonly int stringLen;
private readonly byte delimiterLen;
private readonly bool isSingleCharDelim;
}
};
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 2, 2010 at 11:41 am
Paul White NZ (9/2/2010)
steve-893342 (9/2/2010)
Yes precisely, the number of rows returned from the split is defined as (number of commas) + 1Ok, let's go with that. Thanks for persisting 🙂
That's fantastic Paul:-)
I definitely owe you a pint!
September 23, 2010 at 10:42 pm
Paul White NZ (9/2/2010)
Ok, let's go with that. Thanks for persisting 🙂
CREATE FUNCTION dbo.SplitString_Multi
(
@Input NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
(
sequence INTEGER NULL,
item NVARCHAR (4000) NULL
)
Paul,
I know I should just test this, I might if I had the tools installed and knew how to make/build CLRs, but have you tested if there is much of a performance difference if the CLR didn't handle UNICODE characters? (Would it be better to have two versions, one that handles VARCHAR and one that handles NVARCHAR?)
Same with the sequence field, sometimes I would want that and other times I wouldn't. Could that be an option controlled by a parameter, or should it be yet another version?
I'm thinking I would need 6 different CLR splitters, just as an initial count.
September 24, 2010 at 12:48 am
UMG Developer (9/23/2010)
I know I should just test this, I might if I had the tools installed and knew how to make/build CLRs...
If you trust me, you can just run the CREATE ASSEMBLY and CREATE FUNCTION statements from SSMS. There's no need to build it from the source code.
...but have you tested if there is much of a performance difference if the CLR didn't handle UNICODE characters? (Would it be better to have two versions, one that handles VARCHAR and one that handles NVARCHAR?)
You can't pass anything other than Unicode to SQLCLR code.
Same with the sequence field, sometimes I would want that and other times I wouldn't. Could that be an option controlled by a parameter, or should it be yet another version?
If you don't want the sequence field, simply omit it from your query. There would be no advantage in coding a function that did/did not produce the sequence column.
I'm thinking I would need 6 different CLR splitters, just as an initial count.
I hope you can now see that just one will be fine.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 24, 2010 at 2:28 pm
Paul White NZ (9/24/2010)
If you trust me, you can just run the CREATE ASSEMBLY and CREATE FUNCTION statements from SSMS. There's no need to build it from the source code.
For this one, but for other modifications I would need to build it unless you want to make all the test versions for me.
You can't pass anything other than Unicode to SQLCLR code.
Good to know, thanks!
If you don't want the sequence field, simply omit it from your query. There would be no advantage in coding a function that did/did not produce the sequence column.
So there is no overhead in the SQLCLR calculating/returning the column when it isn't involved in the query?
I hope you can now see that just one will be fine.
Assuming there is no performance penalty I am down to two versions, one that returns NVARCHARs and one that returns INTs. I guess I could CAST/CONVERT the return from the one you provided, but would it be more efficient to let the CLR do that? Do you have time to build another SQLCLR splitter that returns INTs so we can test the performance difference? 😉
September 24, 2010 at 10:30 pm
UMG Developer (9/24/2010)
For this one, but for other modifications I would need to build it unless you want to make all the test versions for me.
No I don't 😛
If you want to play with SQLCLR you'll have to learn how to build it at some stage. It's not hard though. Downloading Visual C# 2010 Express is probably the easiest way:
http://www.microsoft.com/express/Downloads/#2010-Visual-CS
So there is no overhead in the SQLCLR calculating/returning the column when it isn't involved in the query?
The incremental runtime cost of calculating the sequence number (adding 1 to a variable) will be immeasurable, yes. SQL Server doesn't fetch function columns it doesn't need.
Assuming there is no performance penalty I am down to two versions, one that returns NVARCHARs and one that returns INTs. I guess I could CAST/CONVERT the return from the one you provided, but would it be more efficient to let the CLR do that?
It depends. The same considerations apply as if you were writing a T-SQL function.
Do you have time to build another SQLCLR splitter that returns INTs so we can test the performance difference?
Not right now. If I get a minute later tonight I might do - it's pretty trivial, but I can see it might be useful as a learning thing.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 24, 2010 at 11:10 pm
Paul White NZ (9/24/2010)
Do you have time to build another SQLCLR splitter that returns INTs so we can test the performance difference?
Not right now. If I get a minute later tonight I might do - it's pretty trivial, but I can see it might be useful as a learning thing.
Sounds good. The more examples that are meaningful for me the better in helping get my foot in the SQLCLR water. Thanks!
September 24, 2010 at 11:39 pm
Ok, this one produces integers. I chose to return NULL if the input string contains elements that aren't integers.
CREATE ASSEMBLY [Utility]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103004A899D4C0000000000000000E00002210B010800000E000000060000000000001E2D0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000C82C000053000000004000000803000000000000000000000000000000000000006000000C000000282C00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000240D000000200000000E000000020000000000000000000000000000200000602E7273726300000008030000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000002D000000000000480000000200050068220000C00900000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000D2026F1300000A2D1A036F1300000A2D12026F1400000A036F1400000A73050000062A168D19000001168D1900000173050000062A000000133003003D0000000100001102A5030000020B0312017B01000004281500000A81050000010412017B020000041200281600000A2D077E1700000A2B0606281500000A81050000012A1E02281800000A2A3E02037D0100000402047D020000042A00000003300300530000000000000002281800000A02037D0600000402038E697D0800000402047D0700000402048E69D27D0900000402027B0900000417FE017D0A00000402167D0500000402167D0300000402027B09000004155A7D040000042ABE027B05000004027B06000004027B03000004027B04000004027B0300000459731A00000A73040000068C030000022A0013300300C90000000200001102257B0500000417587D05000004027B04000004027B080000043202162A02027B04000004027B09000004587D03000004027B030000040A2B65170B027B0A0000042C16027B060000040693027B0700000416932E39160B2B35160C2B28060858027B080000042F14027B0600000406085893027B0700000408932E04160B2B0E081758D20C08027B0900000432CF072C0902067D04000004172A0617580A06027B08000004329202027B04000004027B09000004587D0300000402027B080000047D04000004172A5A02167D0300000402027B09000004155A7D040000042A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000088030000237E0000F40300004004000023537472696E6773000000003408000008000000235553003C0800001000000023475549440000004C0800007401000023426C6F6200000000000000020000015717A2010902000000FA253300160000010000001D000000040000000A0000000800000009000000010000001A000000100000000200000001000000010000000100000001000000020000000200000000000A0001000000000006005D005600060064005600060081006E000A00AE0099000A00C90099000A0085016A010600C401A5010600FB01E90106001202E90106002F02E90106004E02E90106006702E90106008002E90106009B02E9010600B602E9010600CF02A5010600E302A5010600F102E90106000A03E90106003A03270353004E03000006007D035D0306009D035D030A00C7036A010600F103560006000204560006001604A50106002C04A5010600370456000000000001000000000001000100010010001A0000000500010001000B0110002F000000090001000400020010003C0000000500030005000600E60022000600EF00250001000F0122000100170122000100E600220021001F013E00210029013E0021003301220021003D01420021004A0145005020000000009600B7000A0001008820000000009600D20013000300D120000000008618E0001E000600D920000000008618E00028000600EC20000000008618E0002E0008004B2100000000E609F40036000A007C2100000000E60100013A000A00512200000000E60109011E000A0000000100640100000200970100000100A10102000200E60002000300EF0000000100D10100000200DA0100000100DF0100000200970104000D003100E0001E003900E0001E004100E00072004900E00072005100E00072005900E00072006100E00072006900E00072007100E00072007900E00072008100E00077008900E00072009100E00072009900E0007200A100E0007C00B100E0008200B900E0001E00C100E0001E002100DC033A002100E703AE002900F603B300D1000804B90029001104C0000900E0001E00D900E000CA00E900E000D00020009300870024000B004C002E003B00DE002E001B00DE002E002300EB002E002B00EB002E003300EB002E00630009012E008B0052012E004B00EB002E005B00EB002E004300F1002E00730033012E007B0040012E008300490144000B005F00C400D8000400010000005C014800020006000300048000000100000000000000000000000000BB03000002000000000000000000000001004D000000000002000000000000000000000001008D0000000000030002000400020000000000003C4D6F64756C653E00537472696E675574696C732E646C6C0055736572446566696E656446756E6374696F6E73004F75747075745265636F72640053706C6974537472696E674D756C7469006D73636F726C69620053797374656D004F626A6563740056616C7565547970650053797374656D2E436F6C6C656374696F6E730049456E756D657261746F720053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730053706C6974537472696E675F4D756C74690053716C496E7433320046696C6C526F775F4D756C7469002E63746F720073657175656E6365006974656D006765745F43757272656E74004D6F76654E657874005265736574006C617374506F73006E657874506F7300746865537472696E670064656C696D6974657200737472696E674C656E0064656C696D697465724C656E00697353696E676C654368617244656C696D0043757272656E7400496E707574004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650044656C696D69746572006F626A0053797374656D2E52756E74696D652E496E7465726F705365727669636573004F75744174747269627574650053657175656E6365004974656D00546865537472696E670053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500537472696E675574696C730053716C46756E6374696F6E417474726962757465006765745F49734E756C6C006765745F56616C75650043686172006F705F496D706C6963697400496E743332005472795061727365004E756C6C005374727563744C61796F7574417474726962757465004C61796F75744B696E6400537472696E6700000000032000000000008C9EB346FD208D41820847BF66905F000008B77A5C561934E089080002120D121112110A0003011C1011151011150320000102060802060E05200201080E072002011D031D030320001C0320000203061D030206050206020328001C12010001005408074D617853697A65FFFFFFFF12010001005408074D617853697A65FF000000042001010E042001010205200101115504200101082601000100540E1146696C6C526F774D6574686F644E616D650D46696C6C526F775F4D756C74690420001D03050001111508060002020E10080306111505070208110C052001011171072003011D0308080507030802050C0100075574696C697479000005010000000017010012436F7079726967687420C2A920203230313000002901002436616266323165302D393063382D343865392D626165362D36666636363966336562653200000C010007312E302E302E3000000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000000004A899D4C000000000200000084000000442C0000440E00005253445326D0FEDE5ECCC3408D78EA0B0EDDDEE913000000433A5C446F63756D656E747320616E642053657474696E67735C5061756C5C4D7920446F63756D656E74735C56697375616C2053747564696F20323031305C50726F6A656374735C5574696C6974795C6F626A5C52656C656173655C537472696E675574696C732E70646200F02C000000000000000000000E2D0000002000000000000000000000000000000000000000000000002D000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000B00200000000000000000000B00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00410020000010053007400720069006E006700460069006C00650049006E0066006F000000EC0100000100300030003000300030003400620030000000380008000100460069006C0065004400650073006300720069007000740069006F006E00000000005500740069006C006900740079000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000040001000010049006E007400650072006E0061006C004E0061006D006500000053007400720069006E0067005500740069006C0073002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100300000004800100001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000053007400720069006E0067005500740069006C0073002E0064006C006C000000300008000100500072006F0064007500630074004E0061006D006500000000005500740069006C006900740079000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000203D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION
dbo.SplitString_Multi
(
@Input NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
(
sequence INTEGER NULL,
item INTEGER NULL
)
WITH EXECUTE AS CALLER
AS EXTERNAL
NAME Utility.UserDefinedFunctions.SplitString_Multi;
GO
Source:
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(
FillRowMethodName = "FillRow_Multi"
)
]
public static IEnumerator SplitString_Multi(
[SqlFacet(MaxSize = -1)]
SqlChars Input,
[SqlFacet(MaxSize = 255)]
SqlChars Delimiter
)
{
return (
(Input.IsNull || Delimiter.IsNull) ?
new SplitStringMulti(new char[0], new char[0]) :
new SplitStringMulti(Input.Value, Delimiter.Value));
}
private struct OutputRecord
{
public int sequence;
public string item;
public OutputRecord(int Sequence, string Item)
{
this.sequence = Sequence;
this.item = Item;
}
}
public static void FillRow_Multi(object obj, out SqlInt32 sequence, out SqlInt32 item)
{
int int_item;
OutputRecord r = (OutputRecord)obj;
sequence = r.sequence;
item = Int32.TryParse(r.item, out int_item) ? int_item : SqlInt32.Null;
}
public class SplitStringMulti : IEnumerator
{
public SplitStringMulti(char[] TheString, char[] Delimiter)
{
theString = TheString;
stringLen = TheString.Length;
delimiter = Delimiter;
delimiterLen = (byte)(Delimiter.Length);
isSingleCharDelim = (delimiterLen == 1);
sequence = 0;
lastPos = 0;
nextPos = delimiterLen * -1;
}
#region IEnumerator Members
public object Current
{
get
{
return new OutputRecord(sequence, new string(theString, lastPos, nextPos - lastPos));
}
}
public bool MoveNext()
{
sequence++;
if (nextPos >= stringLen)
return false;
else
{
lastPos = nextPos + delimiterLen;
for (int i = lastPos; i < stringLen; i++)
{
bool matches = true;
if (isSingleCharDelim)
{
if (theString != delimiter[0])
matches = false;
}
else
{
for (byte j = 0; j < delimiterLen; j++)
{
if (((i + j) >= stringLen) || (theString != delimiter[j]))
{
matches = false;
break;
}
}
}
if (matches)
{
nextPos = i;
return true;
}
}
lastPos = nextPos + delimiterLen;
nextPos = stringLen;
return true;
}
}
public void Reset()
{
lastPos = 0;
nextPos = delimiterLen * -1;
}
#endregion
private int lastPos;
private int nextPos;
private int sequence;
private readonly char[] theString;
private readonly char[] delimiter;
private readonly int stringLen;
private readonly byte delimiterLen;
private readonly bool isSingleCharDelim;
}
};
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 25, 2010 at 12:05 am
Paul White NZ (9/24/2010)
Ok, this one produces integers. I chose to return NULL if the input string contains elements that aren't integers.
Perfect, I will have to try that out. Thanks!
One question, why does this version not have the "TableDefinition" in the "Microsoft.SqlServer.Server.SqlFunction" definition? Is it not required?
September 25, 2010 at 12:08 am
Paul White NZ (9/24/2010)
...but have you tested if there is much of a performance difference if the CLR didn't handle UNICODE characters? (Would it be better to have two versions, one that handles VARCHAR and one that handles NVARCHAR?)
You can't pass anything other than Unicode to SQLCLR code.
What about the performance difference between passing in a NVARCHAR(MAX) and NVARCHAR(4000)? (Of course 4000 characters is fairly small for wanting to use SQLCLR, maybe when the source string is that small using the tally method is better?)
Viewing 15 posts - 31 through 45 (of 49 total)
You must be logged in to reply to this topic. Login to reply