Split String SQL function

If you work with SQL Server you have, or at some point will, run into a situation where you have a string of separated values that you may need to involve in a join or simply generate a list out of. So, you need to split the string and dump the values in a table. Following is a simple Table-valued function that takes a string and a divider as parameters and returns a table containing the values into a list form (one value for each row). The parameters are defined as a varchar(1024) for the string of values and char(1) for the divider but you can change those based on your needs.

CREATE FUNCTION SplitString
(
    @SeparatedValues VARCHAR(1024),
    @Divider CHAR(1)
)
RETURNS    @ListOfValues TABLE ([value] VARCHAR(50))
AS  BEGIN
      DECLARE @DividerPos1 int, @DividerPos2 int
      SET @DividerPos1 = 1
      SET @DividerPos2 = CHARINDEX(@Divider, @SeparatedValues, 0)

     WHILE @DividerPos2 > 0
           BEGIN
                  INSERT INTO @ListOfValues VALUES (SUBSTRING(@SeparatedValues, @DividerPos1, @DividerPos2 - @DividerPos1))
                  SET @DividerPos1 = @DividerPos2 + 1
                  SET @DividerPos2 = CHARINDEX(@Divider, @SeparatedValues, @DividerPos1)
            END
           -- Now get the last value if there is onw
                  IF @DividerPos1 <= LEN(@SeparatedValues)
                       INSERT INTO @ListOfValues VALUES (SUBSTRING(@SeparatedValues, @DividerPos1, LEN(@SeparatedValues) - @DividerPos1 + 1))

        RETURN
  END
GO

Once you create the function you can call it like this:
   SELECT * FROM [SplitString] ('value1|value2|value3', '|')
This will return: 
   value1
   value2
   value3
  
Note that if the string starts with a divider like '|value1|value2|value3' then the first value returned will be a blank value.