SQL String Split Function

Overview:

 The SQL code gives you the string spilt functionality of given delimiter. You can define your own delimiter character, in below code I have used ‘:’ as a delimiter. If you want to use other delimiter replace ‘:’ with your character.

SQL Code:  

CREATE FUNCTION dbo.fnSplitString (@string nvarchar(MAX))

   RETURNS @tblTemp TABLE (splittedValue nvarchar(MAX) NOT NULL) AS

BEGIN

   DECLARE @posIndex        int,

           @nextposIndex    int,

           @valuelen   int

   SELECT @posIndex = 0, @nextposIndex = 1

   WHILE @nextposIndex > 0

   BEGIN

      SELECT @nextposIndex = charindex(':', @string, @posIndex + 1)

      SELECT @valuelen = CASE WHEN @nextposIndex > 0

                              THEN @nextposIndex

                              ELSE len(@string) + 1

                         END - @posIndex - 1

      INSERT @tblTemp (splittedValue)

         VALUES (substring(@string, @posIndex + 1, @valuelen))

      SELECT @posIndex = @nextposIndex

   END

   RETURN

END

Usage:

SELECT      *

FROM  dbo.fnSplitString('ABC:DEF:GEH')

Result: