May 11, 2012 at 6:22 am
This is a very nice function. But, to even consider its use, it would be helpful to know how it compares in terms of execution performance to other similar functions that have been written before.
For example I have 2 versions of this split function. Would be great if someone is able to provide some metrics and a nice writeup. I take no credit for writing either of them. Beings with higher SQL mojo deserve the credit.
version 1:
/*\
SET STATISTICS TIME ON;
SELECT * FROM dbo.ufn_Split('03680027,08563027,03682027,03677003,03683027,17948027,03702003,17948003,15636003,03620003,03299003,03014003,03679027,08563003,03013003,15636027,00781003,35879049,03682003,03683003,03679003,03680003,30441003,02747049,02748049,02749049,03300003,02981003,02981027,02981004,02982003,02982027,02982004,02983003,02983027,02983004,06921003,02974003,02974027,02974004,02975003,02975027,02975004,02973003,02973027,02973004', DEFAULT)
SET STATISTICS TIME OFF;
\*/
ALTER FUNCTION [dbo].[split]
(
@DelimitedString AS VARCHAR(MAX),
@Delimiter AS CHAR = N','
)
RETURNS @Result TABLE(
TokenVARCHAR(128)
)
AS
BEGIN
INSERT
INTO@Result (
Token
)
SELECTSUBSTRING(
@Delimiter + @DelimitedString + @Delimiter,
NumberId + 1,
CHARINDEX(@Delimiter, @Delimiter + @DelimitedString + @Delimiter,NumberId + 1) - NumberId - 1
) AS Token
FROMdbo.tbl_Number WITH (NOLOCK)
WHERENumberId >= 1
ANDNumberId < LEN(@Delimiter + @DelimitedString + @Delimiter) - 1
ANDSUBSTRING(@Delimiter + @DelimitedString + @Delimiter, NumberId, 1) = @Delimiter
RETURN
END
version 2:
ALTER FUNCTION [dbo].[split_2]
(
@s-2VARCHAR(8000),
@sepCHAR(1) = N','
)
RETURNS TABLE
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT1,
1,
CHARINDEX(@sep, @s-2)
UNION ALL
SELECTpn + 1,
stop + 1,
CHARINDEX(@sep, @s-2, stop + 1)
FROMPieces
WHEREstop > 0
)
SELECTpn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop - start ELSE 8000 END) AS s
FROMPieces
)
May 11, 2012 at 7:24 am
Ignore this topic and refer to topic http://www.sqlservercentral.com/Forums/Topic1298365-3151-1.aspx?Update=1.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply