Skip to main content

Posts

Showing posts from January, 2011

SQL function to split string into table

Sometime in SQL server we need to split a long character string....here I am writing function to split string.....with string and delimiter as parameter and it returns table of string as result.
Split Function SETANSI_NULLSON GO
SETQUOTED_IDENTIFIERON GO
CREATEFUNCTION [dbo].[funSplit](     @sInputList VARCHAR(8000)-- List of delimited items , @sDelimiter VARCHAR(8000)=','-- delimiter that separates items )RETURNS @List TABLE (item VARCHAR(8000))
BEGIN DECLARE @sItem VARCHAR(8000) WHILECHARINDEX(@sDelimiter,@sInputList,0)<> 0 BEGIN SELECT                 @sItem =RTRIM(LTRIM(SUBSTRING(@sInputList, 1, CHARINDEX(@sDelimiter,@sInputList,0)-1))),                 @sInputList =RTRIM(LTRIM(SUBSTRING(@sInputList, CHARINDEX(@sDelimiter,@sInputList,0) +LEN(@sDelimiter),LEN(@sInputList))))
IFLEN(@sItem)> 0 INSERTINTO @List SELECT @sItem