How to make and use Split function in sql Server

1. Create a Function in SQL as follows:

Create FUNCTION dbo.StringSplit
(
@SplitStr nvarchar(2000),
@SplitChar nvarchar(20)
)
RETURNS @RtnValue table
(
Data nvarchar(2000)
)
AS
BEGIN
Declare @Count int
Set @Count = 1
While (Charindex(@SplitChar,@SplitStr)>0)
Begin
Insert Into @RtnValue (Data)
Select
Data = ltrim(rtrim(Substring(@SplitStr,1,Charindex(@SplitChar,@SplitStr)-1)))
Set @SplitStr = Substring(@SplitStr,Charindex(@SplitChar,@SplitStr)+1,len(@SplitStr))
Set @Count = @Count + 1
End
Insert Into @RtnValue (Data)
Select Data = ltrim(rtrim(@SplitStr))
Return
END

2. you need to pass two parameter one is string and otherone is spliting character
here is example

select * from SplitString('Contact-No-Deal\Email\08-30-2010\SP10000175-08-30-2010-16-56-28-5322-BalanceRequest.doc', '\')

output will be like this:

Contact-No-Deal
Email
08-30-2010
SP10000175-08-30-2010-16-56-28-5322-BalanceRequest.doc