-
-
CREATE FUNCTION usf_SplitString (@delimitedString nvarchar(max), @mDelimiter char(1) = 'y')<br />Returns @tblStringToTable Table (Colvalue nvarchar(50))<br />AS<br />BEGIN<br />Declare @colvalue nvarchar(50)<br />Declare @startpos int, @length int<br />while LEN(@delimitedString) > 0<br />BEGIN<br /> SET @StartPos = CHARINDEX(@mDelimiter, @DelimitedString)<br /> IF @StartPos < 0 set @StartPos = 0<br /> SET @Length = LEN(@DelimitedString) - @StartPos - 1<br /> IF @Length < 0 SET @Length = 0<br /> IF @StartPos > 0<br /> BEGIN<br /> SET @colvalue = SUBSTRING(@delimitedString,1,@StartPos - 1)<br /> Set @delimitedString = SUBSTRING(@delimitedString. @StartPos+ 1, LEN(@delimitedString ) - @StartPos)<br />END</p>
<p>Else<br />BEGIN</p>
<p> Set @colvalue = @delimitedString;<br /> Set @delimitedString = ''<br />END<br />IF @Colvalue != ''<br /> IF not exists(Select 1 from @delimitedString where Colvalue = @Colvalue)<br /> INSERT @tblStringToTable (Colvalue) VALUES (@Colvalue)<br />END<br /> RETURN<br />END<br />GO</p>
<p>See Below for example usage - don't forget you must specify database owner</p>
<p>select * from dbo.usf_SplitString ('1,2,3,4',',')</p>
<p>Returns a table</p>
<p> Colvalue<br />1<br />2<br />3<br />4</p>'
