How to split the string in SQL server

Sometimes there is a requirement when we want to get the individual words from a complete string. The question comes how can we do that in database level only so that our application will get the desired result from the database itself.

Below is the desired output in the split word format

To achieve this we have to use the "substring" method/function to split the string. Here we are creating an user defined function which will return the result in the split format.

Here is the logic for the function.

CREATE FUNCTION [dbo].[SplitData]
(
@RowData varchar(8000),
@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