Overblog Follow this blog
Edit post Administration Create my blog
Balavardhan Reddy Narani

Function To Split a String and Store in Table in SQLServer2005.

March 7 2009 , Written by Balavardhan Published on #SQL Server2005

Here By I am providing a function to Split the String by deliminator in Sql Server

This Function requires two Parameters

1). String to Split
2). Deliminator  to split the string based on character

SQL Server Function

CREATE FUNCTION [dbo].[udf_List2Table]
(
@List VARCHAR(MAX),@Delim CHAR
)
RETURNS
@ParsedList TABLE(item VARCHAR(MAX))
AS
BEGIN
DECLARE @item VARCHAR(MAX), @Pos INT
       SET @List = LTRIM(RTRIM(@List))+ @Delim
       SET @Pos = CHARINDEX(@Delim, @List, 1)
WHILE @Pos > 0
BEGIN
      SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
IF @item <> ''
BEGIN
     INSERT INTO @ParsedList (item)
                                VALUES (CAST(@item AS VARCHAR(MAX)))
END
SET @List = RIGHT(@List, LEN(@List) - @Pos)
SET @Pos = CHARINDEX(@Delim, @List, 1)
END
RETURN
END


How Use the Above Function.

SELECT * FROM udf_List2Table ('1,2,3,4,5,6,7,8,9,10',',')

Resut from the Abive function

Output
1
2
3
4
5
6
7
8
9
10

-

Share this post

Repost 0

Comment on this post