(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) https://esdbp.createaforum.com --------------------------------------------------------- ***************************************************** (DIR) Return to: Scripts ***************************************************** #Post#: 111-------------------------------------------------- Splliter Function optimized By: srinivasma_exceldbp Date: November 28, 2014, 5:26 am --------------------------------------------------------- use master go CREATE function [dbo].[fn_splitter] ( @param nvarchar(max), @delimiter char(1) ) returns @t table (val nvarchar(max), seq int) as begin set @param = @param + @delimiter ;with a as ( select cast(1 as bigint) f, charindex(@delimiter, @param) t, 1 seq union all select t + 1, charindex(@delimiter, @param, t + 1), seq + 1 from a where charindex(@delimiter, @param, t + 1) > 0 ) insert @t select substring(@param, f, t - f), seq from a option (maxrecursion 0) return end SET NOCOUNT ON; GO -- How to execute select t.val from [dbo].[fn_splitter] ( 'abcd,efghjk,djdfdbd,fdjfdhf,dhdhfjdhfdjh,fdjkfjhdjf,jjfjjdff',' ,') as t go *****************************************************