June 7, 2012 at 2:08 pm
I don't fully understand how the splitter and the tally table works well enough to use it for alternative uses, so I am reaching out to the forum.
We have columns in a table that contain "extents" (which are basically delimited fields within the column itself) Ex:
Col1
0;0;0;0;0;1;0;1.5;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0
In the past someone created a CLR function that pulled out the proper field based upon its position within the extent, however, the source code for that is "lost" and I can't figure out how to recreate it on a new server (where this is currently needed).
Give the VIEW below, how can I use Jeff Moden's splitter/tally table to replace the CLR function?
CREATE VIEW [PUB].[TC-BILL]
AS
SELECT [date-sent-tc]
,[our-invoice]
,CONVERT(FLOAT,dbo.SplitCMD([bill-pay-key],1)) as [bill-pay-key@1]
...
,CONVERT(FLOAT,dbo.SplitCMD([bill-pay-key],20)) as [bill-pay-key@20]
,CONVERT(FLOAT,dbo.SplitCMD([tc-fee-amt],1)) as [tc-fee-amt@1]
...
,CONVERT(FLOAT,dbo.SplitCMD([tc-fee-amt],80)) as [tc-fee-amt@80]
,CONVERT(FLOAT,dbo.SplitCMD([ts-fee-amt],1)) as [ts-fee-amt@1]
...
,CONVERT(FLOAT,dbo.SplitCMD([ts-fee-amt],80)) as [ts-fee-amt@80]
,CONVERT(FLOAT,dbo.SplitCMD([tot-tran-by-fee],1)) as [tot-tran-by-fee@1]
...
,CONVERT(FLOAT,dbo.SplitCMD([tot-tran-by-fee],80)) as [tot-tran-by-fee@80]
FROM [loadprogress].[PUB].[TC-BILL]
GO
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 7, 2012 at 2:11 pm
In the SplitCMD function, is the number the position?
And, are the arguments always one digit (I think that's what I'm seeing in your sample), or are they potentially longer?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 7, 2012 at 2:15 pm
The number is the position, and the value can be 1-80 for some fields, 1-40 in some, and 1-20 in others
Ex:
dbo.SplitCMD([ts-fee-amt],80)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 7, 2012 at 2:22 pm
in his example, at least one value was "1.5", so th3ey are not single character items.
Lowell
June 7, 2012 at 2:26 pm
Would soemthing like this work, or is it defeating the purpose of Jeff's work of art?
CREATE FUNCTION [dbo].[fx_SplitExtents]
(
@Extent VARCHAR(8000), @Position int
)
/*
select dbo.fx_SplitExtents('50;160;99;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0', 1)
*/
RETURNS decimal(18,4)
BEGIN
RETURN( SELECT split.Item
FROM SSRS_Run_Reports.dbo.fx_DelimitedSplit8k(@extent,';') split
WHERE split.ItemNumber = @Position)
END
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 7, 2012 at 2:28 pm
looks like it's a combination of Splitting and a pivot;
for specific values, this seems to get teh data back into "column" form; you'd need three split+ pivots in your example, one for [bill-pay-key] and another fo r[ts-fee-amt], and finally [tot-tran-by-fee]
/*
1880
01.5NULL
*/
;with cte as
(
select * FROM dbo.DelimitedSplit8K('0;0;0;0;0;1;0;1.5;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0',';')
)
select * from
( select * from cte where ItemNumber IN('1','8','80') ) pivot_handle
pivot
(MAX(Item) for ItemNumber in ([1],[8],[80])) pivot_table
Lowell
June 7, 2012 at 8:44 pm
MyDoggieJessie (6/7/2012)
Would soemthing like this work, or is it defeating the purpose of Jeff's work of art?
CREATE FUNCTION [dbo].[fx_SplitExtents]
(
@Extent VARCHAR(8000), @Position int
)
/*
select dbo.fx_SplitExtents('50;160;99;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0', 1)
*/
RETURNS decimal(18,4)
BEGIN
RETURN( SELECT split.Item
FROM SSRS_Run_Reports.dbo.fx_DelimitedSplit8k(@extent,';') split
WHERE split.ItemNumber = @Position)
END
I tested this against the older CLR function and it seems to do the exact same thing and I don't see any performance hit...unless anyone has a better option the above solution is what I am going to use.
As always, I sincerely appreciate the feedback! Thanks guys!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 7, 2012 at 8:57 pm
MyDoggieJessie (6/7/2012)
I don't fully understand how the splitter and the tally table works well enough to use it for alternative uses, so I am reaching out to the forum.We have columns in a table that contain "extents" (which are basically delimited fields within the column itself) Ex:
Col1
0;0;0;0;0;1;0;1.5;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0
In the past someone created a CLR function that pulled out the proper field based upon its position within the extent, however, the source code for that is "lost" and I can't figure out how to recreate it on a new server (where this is currently needed).
Give the VIEW below, how can I use Jeff Moden's splitter/tally table to replace the CLR function?
CREATE VIEW [PUB].[TC-BILL]
AS
SELECT [date-sent-tc]
,[our-invoice]
,CONVERT(FLOAT,dbo.SplitCMD([bill-pay-key],1)) as [bill-pay-key@1]
...
,CONVERT(FLOAT,dbo.SplitCMD([bill-pay-key],20)) as [bill-pay-key@20]
,CONVERT(FLOAT,dbo.SplitCMD([tc-fee-amt],1)) as [tc-fee-amt@1]
...
,CONVERT(FLOAT,dbo.SplitCMD([tc-fee-amt],80)) as [tc-fee-amt@80]
,CONVERT(FLOAT,dbo.SplitCMD([ts-fee-amt],1)) as [ts-fee-amt@1]
...
,CONVERT(FLOAT,dbo.SplitCMD([ts-fee-amt],80)) as [ts-fee-amt@80]
,CONVERT(FLOAT,dbo.SplitCMD([tot-tran-by-fee],1)) as [tot-tran-by-fee@1]
...
,CONVERT(FLOAT,dbo.SplitCMD([tot-tran-by-fee],80)) as [tot-tran-by-fee@80]
FROM [loadprogress].[PUB].[TC-BILL]
GO
Does the combination of the [date-sent-tc] and [our-invoice] columns in the [loadprogress].[PUB].[TC-BILL] table make unique rows in the view? If not, what in the table makes a row unique?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 7, 2012 at 9:03 pm
That's a very good question and in all honesty I don't know...I believe there's a uid (row guid/unique identifier) that is assigned to each row...but I'll have to check with the guys on that system to see what columns could be used to constitute a unique record
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 8, 2012 at 7:09 am
The reason why I asked about uniqueness is because the original code for the view used a scalar function even if it was a CLR. The DelimitedSplit8K is an Inline Table Valued function (iTVF) and, although that makes it very fast in this case, that also makes it very different. As you've seen in the code from the others, we pretty much need to do a CROSS TAB or PIVOT and for that, we need a way to uniquely identify a row.
Changing my function to a scalar function to find a numbered element could easily be done but I believe it would terribly slow compared to the CLR.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 9, 2012 at 8:33 am
MyDoggieJessie (6/7/2012)
In the past someone created a CLR function that pulled out the proper field based upon its position within the extent, however, the source code for that is "lost" and I can't figure out how to recreate it on a new server (where this is currently needed).
Here's a SQLCLR implementation:
CREATE ASSEMBLY SplitElement
FROM FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300BB82D34F0000000000000000E00002210B010800000800000006000000000000EE2700000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000009427000057000000004000000003000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000F4070000002000000008000000020000000000000000000000000000200000602E72737263000000000300000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E00000000000000000000000000004000004200000000000000000000000000000000D0270000000000004800000002000500CC200000C80600000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133004006700000001000011026F0E00000A2D0404162F067E0F00000A2A026F1000000A0A068E690B160C160D2B2606099303331C0417591002042D100608090859731100000A731200000A2A0917580C0917580D090732D6041733100608070859731100000A731200000A2A7E0F00000A2A1E02281300000A2A0042534A4201000100000000000C00000076322E302E35303732370000000005006C000000F0010000237E00005C0200008802000023537472696E677300000000E40400000800000023555300EC040000100000002347554944000000FC040000CC01000023426C6F620000000000000002000001471502000900000000FA2533001600000100000011000000020000000200000003000000130000000B00000001000000010000000200000000000A000100000000000600400039000A00680053000A00720053000600B800A6000600CF00A6000600EC00A60006000B01A60006002401A60006003D01A60006005801A60006007301A6000600AB018C010600BF01A6000600F801D80106001802D8010A00510236020600800239000000000001000000000001000100010010001B00000005000100010050200000000096007B000A000100C320000000008618880013000400000001008E00000002009400000003009E00210088001700290088001700310088001700390088001700410088001700490088001700510088001700590088001700610088001C00690088001700710088002100790088001300810088001300190066026E01110071027201190076027601890088007B0111008800170009008800130020006B0026002E002B009D012E0013009D012E001B009D012E0023009D012E000B008B012E0033009D012E003B009D012E004B009D012E005B00A3012E006300AC0183010480000001000000C011052B0000000000007B000000020000000000000000000000010030000000000002000000000000000000000001004700000000000000003C4D6F64756C653E0053706C6974456C656D656E742E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E670053716C43686172730053706C6974456C656D656E74002E63746F7200496E7075740044656C696D6974657200456C656D656E740053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E417474726962757465006765745F49734E756C6C004E756C6C006765745F56616C756500537472696E67000000032000000000003FA0BA87EC9DCC4FA5871CD99F291CA70008B77A5C561934E0890800031109120D030803200001042001010E042001010204200101088146010004005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E6973746963015402094973507265636973650103200002030611090420001D03072003011D0308080707041D030808081101000C53706C6974456C656D656E7400000501000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100BC2700000000000000000000DE270000002000000000000000000000000000000000000000000000D02700000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000A40200000000000000000000A40234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100052BC01100000100052BC0113F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00404020000010053007400720069006E006700460069006C00650049006E0066006F000000E0010000010030003000300030003000340062003000000044000D000100460069006C0065004400650073006300720069007000740069006F006E0000000000530070006C006900740045006C0065006D0065006E0074000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003500340034002E00310031003000310033000000000044001100010049006E007400650072006E0061006C004E0061006D0065000000530070006C006900740045006C0065006D0065006E0074002E0064006C006C00000000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000004C00110001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530070006C006900740045006C0065006D0065006E0074002E0064006C006C000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003500340034002E00310031003000310033000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003500340034002E0031003100300031003300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000F03700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION dbo.SplitElement
(
@Input nvarchar(MAX),
@Delimiter nchar(1),
@Element int
)
RETURNS nvarchar(4000)
AS EXTERNAL NAME SplitElement.UserDefinedFunctions.SplitElement;
Example:
DECLARE
@Input nvarchar(max) = N'A;B;C;D;E;F;G';
SELECT
dbo.SplitElement(@Input, N';', 1),
dbo.SplitElement(@Input, N';', 2),
dbo.SplitElement(@Input, N';', 3),
dbo.SplitElement(@Input, N';', 4),
dbo.SplitElement(@Input, N';', 5),
dbo.SplitElement(@Input, N';', 6),
dbo.SplitElement(@Input, N';', 7);
Source:
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[SqlFunction
(
DataAccess=DataAccessKind.None,
SystemDataAccess=SystemDataAccessKind.None,
IsDeterministic=true,
IsPrecise=true
)
]
public static SqlString SplitElement
(
SqlChars Input,
char Delimiter,
int Element
)
{
// Check parameters
if (Input.IsNull || Element < 0)
{
return SqlString.Null;
}
// Initialize the search
char[] input = Input.Value;
int length = input.Length;
int start = 0;
// Loop to find the nth element
for (int pos = 0; pos < length; pos++)
{
// Found a delimiter?
if (input[pos] == Delimiter)
{
// Yes, reduce the counter
Element--;
// Found the one to return?
if (Element == 0)
{
// Return this element
return new SqlString(new string(input, start, pos - start));
}
// Set the next element start point
start = pos + 1;
}
}
// Last element
if (Element == 1)
{
return new SqlString(new string(input, start, length - start));
}
return SqlString.Null;
}
};
edit: fixed last element
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 9, 2012 at 10:10 am
Paul,
I'm not a C# programmer so I don't actually know what the problem is but the CLR is returning NULL for the last column.
(Variable assignent modified for those that want to play in 2k5)
DECLARE
@Input nvarchar(max)
SELECT @Input = N'A;B;C;D;E;F;G';
SELECT
Col01 = dbo.SplitElement(@Input, N';', 1),
Col02 = dbo.SplitElement(@Input, N';', 2),
Col03 = dbo.SplitElement(@Input, N';', 3),
Col04 = dbo.SplitElement(@Input, N';', 4),
Col05 = dbo.SplitElement(@Input, N';', 5),
Col06 = dbo.SplitElement(@Input, N';', 6),
Col07 = dbo.SplitElement(@Input, N';', 7);
Results:
Col01 Col02 Col03 Col04 Col05 Col06 Col07
A B C D E F NULL
[EDIT] Corrected misalignment of the results above.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 9, 2012 at 10:23 am
MyDoggieJessie (6/7/2012)
In the past someone created a CLR function that pulled out the proper field based upon its position within the extent, however, the source code for that is "lost" and I can't figure out how to recreate it on a new server (where this is currently needed).
The way that Paul posted the implementation of the CLR reminded me of something. You don't actually need the source code to the CLR to copy it to another server. If you right click on the CLR function and tell it to script the code, you'll end up with something like the first snippet (the CREATE ASSEMBLY) of code that Paul posted and that can be executed on another machine to create the function.
If you were also to post that code, I'd be happy to do a performance test for you. Although CLRs are usually very good for string manipulation, sometimes they're not quite as fast as a T-SQL solution. As with all else in SQL Server, the only way to know for sure is to do a test.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 9, 2012 at 11:13 am
Jeff Moden (6/9/2012)
I'm not a C# programmer so I don't actually know what the problem is but the CLR is returning NULL for the last column.
I always forget the last element in the list may not have a terminator! Code updated in the original post.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 9, 2012 at 11:28 am
SQL Kiwi (6/9/2012)
Jeff Moden (6/9/2012)
I'm not a C# programmer so I don't actually know what the problem is but the CLR is returning NULL for the last column.I always forget the last element in the list may not have a terminator! Code updated in the original post.
Very cool. Thanks for the update to the code Paul.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply