split string function (table valued function) in sql server

byZafar Ali Khan Post a Comment
split string function (table valued function) in sql server
 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE FUNCTION [dbo].[fn_split](@pCommaSeparatedString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
     -- enough to cover NVARCHAR(4000)
  WITH E1(N) AS (
                 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 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pCommaSeparatedString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pCommaSeparatedString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pCommaSeparatedString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemId = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pCommaSeparatedString, l.N1, l.L1)
   FROM cteLen l
;

Another Method 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Create FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable
TABLE (nameIndex int identity(1,1),items varchar(8000))
as
begin
	declare @idx int
	declare
	@slice varchar(8000)

	select @idx = 1
	if len(@String)<1 or @String
	is null return

	while @idx!= 0
	begin
	set @idx =
	charindex(@Delimiter,@String)
	if @idx!=0
	set @slice = left(@String,@idx
	- 1)
	else
	set @slice = @String

	if(len(@slice)>0)
	insert
	into @temptable(Items) values(@slice)

	set @String =
	right(@String,len(@String) - @idx)
	if len(@String) = 0 break
	end

return
end
Tagged In:
Previous Post Next Post

Post a Comment

Post a Comment