Home > SQL Server > SQL Server Function to Split Comma in Text

SQL Server Function to Split Comma in Text


Hi all

try this example to use SQL Server Function to Split Comma in Text

so Open SQL Server Management Studio and create a new SQL Query


create FUNCTION [dbo].[Fn_Split]
(
 @RowData nvarchar(max),
 @SplitOn nvarchar(5)
) 
RETURNS @RtnValue table
(
 Id int identity(1,1),
 Data nvarchar(100)
)
AS 
BEGIN
 Declare @Cnt int
 Set @Cnt = 1

 While (Charindex(@SplitOn,@RowData)>0)
 Begin
  Insert Into @RtnValue (data)
  Select Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
  Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
  Set @Cnt = @Cnt + 1
 End
 Insert Into @RtnValue (data)
 Select Data = ltrim(rtrim(@RowData))
 Return
END

and to use this function

select * from  dbo.Fn_Split ('Item 1,Item 2,Item 3,Item 4,Item 5,Item 6' )

Hope this helps

Good Luck

Advertisements
Categories: SQL Server
  1. October 31, 2011 at 7:43 pm

    Great post, thank you…

  2. yasserzaid
    November 1, 2011 at 12:21 pm

    @Köpa Viagra :- you are welcome hope my post helps you

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: