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 «««12345»»»

Split string using Tally Table Expand / Collapse
Author
Message
Posted Friday, July 2, 2010 5:32 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:22 PM
Points: 20,680, Visits: 32,279
Here is what may be considered a better example of using a Tally table. It is dynamically created with the function. And FYI, it is formatted the way I like to format my code. I've heard some complain that it makes my code look more complex.


USE [SandBox]
GO

/****** Object: UserDefinedFunction [dbo].[DelimitedSplit] Script Date: 07/02/2010 17:28:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE function [dbo].[DelimitedSplit] (
@pString varchar(max),
@pDelimiter char(1)
)
returns table
as
return
with
a1 as (select 1 as N union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1),
a2 as (select
1 as N
from
a1 as a
cross join a1 as b),
a3 as (select
1 as N
from
a2 as a
cross join a2 as b),
--a4 as (select
-- 1 as N
-- from
-- a3 as a
-- cross join a2 as b),
Tally as (select top (len(@pString))
row_number() over (order by a.N) as N
from
a3 as a
cross join a2 as b),
ItemSplit(
ItemOrder,
Item
) as (
SELECT
N,
SUBSTRING(@pDelimiter + @pString + @pDelimiter,N + 1,CHARINDEX(@pDelimiter,@pDelimiter + @pString + @pDelimiter,N + 1) - N - 1)
FROM
Tally
WHERE
N < LEN(@pDelimiter + @pString + @pDelimiter)
AND SUBSTRING(@pDelimiter + @pString + @pDelimiter,N,1) = @pDelimiter --Notice how we find the delimiter
)
select
row_number() over (order by ItemOrder) as ItemID,
Item
from
ItemSplit


GO

declare @Text varchar(max),
@StringDelimiter char(1);

SET @Text = 'This T-SQL will split these sentences into rows.' +
'How many rows will be returned?.' +
'M.a.y.b.e..n.o.n.e.?';
SET @StringDelimiter = '.';

select * from dbo.DelimitedSplit (@Text, @StringDelimiter);




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #947132
Posted Friday, July 2, 2010 6:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, February 22, 2014 1:11 AM
Points: 19, Visits: 40

Can someone explain me how the script given below is working ?
Especially the CASE Statement

DECLARE @Text NVARCHAR(2000)
DECLARE @StringDelimiter CHAR(1)
SELECT @Text = 'This T-sql will split senteneces into rows.'+
'How many rows will be returned?.'+
'M.a.y.b.e..n.n.o.e.?',
@StringDelimiter = '.';
With Tally (Number)
AS
(
SELECT ROW_NUMBER() OVER( ORDER BY (SELECT NULL) )AS Number
UNION ALL
SELECT Number + 1 AS Number
FROM Tally WHERE Number <= LEN(@Text)
)
SELECT CASE WHEN RIGHT(LEFT(@Text,Number),
CASE WHEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) > 0
THEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) - 1
ELSE CHARINDEX(@StringDelimiter,Reverse(LEFT(@Text,Number -1)),0) END) = ''
AND
CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1))) = 0
THEN LEFT(@Text,Number -1)
ELSE RIGHT(LEFT(@Text,Number - 1),

CASE WHEN CHARINDEX(@StringDelimiter,Reverse(LEFT(@Text,Number -1)),0) >0
THEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) -1
ELSE CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) END)
END AS SPLIT FROM TALLY

WHERE(NCHAR(UNICODE(SUBSTRING(@Text,Number,1))) = @StringDelimiter
OR Number - 1 = LEN(@Text))

OPTION (MAXRECURSION 32767)
Post #947135
Posted Friday, July 2, 2010 9:12 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:22 PM
Points: 20,680, Visits: 32,279
ricky70rana (7/2/2010)

Can someone explain me how the script given below is working ?
Especially the CASE Statement

DECLARE @Text NVARCHAR(2000)
DECLARE @StringDelimiter CHAR(1)
SELECT @Text = 'This T-sql will split senteneces into rows.'+
'How many rows will be returned?.'+
'M.a.y.b.e..n.n.o.e.?',
@StringDelimiter = '.';
With Tally (Number)
AS
(
SELECT ROW_NUMBER() OVER( ORDER BY (SELECT NULL) )AS Number
UNION ALL
SELECT Number + 1 AS Number
FROM Tally WHERE Number <= LEN(@Text)
)
SELECT CASE WHEN RIGHT(LEFT(@Text,Number),
CASE WHEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) > 0
THEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) - 1
ELSE CHARINDEX(@StringDelimiter,Reverse(LEFT(@Text,Number -1)),0) END) = ''
AND
CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1))) = 0
THEN LEFT(@Text,Number -1)
ELSE RIGHT(LEFT(@Text,Number - 1),

CASE WHEN CHARINDEX(@StringDelimiter,Reverse(LEFT(@Text,Number -1)),0) >0
THEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) -1
ELSE CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) END)
END AS SPLIT FROM TALLY

WHERE(NCHAR(UNICODE(SUBSTRING(@Text,Number,1))) = @StringDelimiter
OR Number - 1 = LEN(@Text))

OPTION (MAXRECURSION 32767)


I realize you would like to understand what this code is doing, but if you are looking for a delimited split function, I think you will find the code I posted previously a much simplier routine to understand. Please note, however, that there are other routines out there tat may perform better as well. I know mine starts having some performance issues when you start working with character strings that are > 8000 bytes.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #947140
Posted Friday, July 2, 2010 9:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, February 22, 2014 1:11 AM
Points: 19, Visits: 40
Actually you are right that i want to understand how that code is working.Only was able to understand Common Table Expression out of this script. Actually I want to understand how the CASE Statement is working. Do you have an idea about that?

I already gone through with your script and that is less cumbersome than this one and easy to understand. Your post is good help to understand tally table and delimiters.
Post #947142
Posted Friday, July 2, 2010 10:02 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:22 PM
Points: 20,680, Visits: 32,279
ricky70rana (7/2/2010)
Actually you are right that i want to understand how that code is working.Only was able to understand Common Table Expression out of this script. Actually I want to understand how the CASE Statement is working. Do you have an idea about that?

I already gone through with your script and that is less cumbersome than this one and easy to understand. Your post is good help to understand tally table and delimiters.


Without copying the code, reformating it to a more readable style for myself, then working through it, no I really don't. It is overly obfusicated when there are much easier ways of accomplishing the necessary task of a delimited split.

If you really want to understand the code, take the time to reformat the code so that you can understand the flow, then work through it like a computer would.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #947143
Posted Saturday, July 3, 2010 3:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, July 3, 2010 3:01 AM
Points: 14, Visits: 0
Very Critical
Post #947169
Posted Saturday, July 3, 2010 4:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:26 PM
Points: 6,007, Visits: 8,269
ricky70rana (7/2/2010)

Can someone explain me how the script given below is working ?
Especially the CASE Statement

The CASE expression (sorry about that, couldn't resist) -or rather the nested CASE expressions- are, as already mentioned, overly complicated.

Basically, it makes use of LEFT(@Text, Number) to get the first Number characters of the string, then uses CASE to decide if there is a @StringDelimiter somewhere in that last part - if there is, it takes the rightmost bit until the last @SDtringDelimiter, otherwise it takes the whole part.

The nested CASE are required to prevent out-of-bound errors in the SUBSTRING functions.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #947178
Posted Monday, July 5, 2010 7:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 5,358, Visits: 8,916
I have to admit ... I was looking at this and saying "What the heck???"

For all those interested, the latest version of the "DelimitedSplit8k" function can be found here.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #947522
Posted Monday, July 5, 2010 8:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 5,358, Visits: 8,916
jts_2003 (7/2/2010)
I think this is an example of why people don't like to use OVER, since it's so hard to work out what might/will be returned!
I'd like to see a simpler question or articles on how OVER works - any takers?


It's not that hard at all, once you understand it. Did you see this article that was just recently published in April?


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #947540
Posted Monday, July 5, 2010 3:29 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 8, 2014 1:18 PM
Points: 2,664, Visits: 830
Like someone else who replied, I shortcircuited the whole thing & counted the delimiters (well, more specifically the spaces between the delimiters), assuming it was an obfuscation question not a "Haha! Tricked you!" question.

Took one skim of the code under the Tally CTE and my brain went "bleh". Seen much cleaner string-splitters.

An exercise in picking through nested functions & conditional statements if nothing else.





Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
Post #947660
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse