Create Function SplitString ( @RowData varchar(2000), @SplitOn varchar(5) ) Returns @RtnValue Table ( Data nvarchar(100) ) As Begin 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)) End Insert Into @RtnValue (Data) Select Data = ltrim(rtrim(@RowData)) Return End
And sample:
Select * From dbo.SplitString('AA, BBB, CC, DDDEF, GHF', ',')