SPLIT users define function in SQL Server 2005:

We may need to split the string with some separator in SQL Server. To cater this need, I have one function which I am using since very long time and felt to share it with you. Have a look at it and enjoy the power of T-SQL programming.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

/*************************************************************************************************

Purpose: To convert a given string to proper case

Tested on: SQL Server 2005

Date Created:March-19-2007

Examples:

To return splitted value

select * from dbo.splitData('ritesh, a, shah',',')

it will return three row as three word separated by comma will be splitted

*************************************************************************************************/

CREATE FUNCTION [dbo].[SplitData]

(

@RowData nvarchar(2000),

@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

Note: I don’t remember whether I have written this script, modified it or got it somewhere from internet as I am using it since long time.

Reference: Ritesh Shah