function to split concatenated string

Hi,

Create below function

CREATE FUNCTION [dbo].[String_Tokenizer]
(
	@RowData nvarchar(max),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	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

The above function will return a temporary table.

Sample code:

select * from [dbo].[String_Tokenizer]('Business Solutions + Business Solutions in Bangalore + Business Solutions in Mysore','+')

Output:

Data

Business Solutions
Business Solutions in Bangalore
Business Solutions in Mysore

If you have the knowledge, and the possibility to do that, I suggest, you make a .net library with CLR User Defined Functions you might need (see: http://msdn.microsoft.com/en-us/library/w2kae45k(v=vs.80).aspx[^]). In .net, you have so much more possibilities to resolve such simple tasks.
Here is an example, ho to make table-valued clr udf: http://msdn.microsoft.com/en-us/library/ms131103.aspx[^]