Split CSV numbers into Table in SQL Server

--- From string to table




CREATE FUNCTION [dbo].[ConvertCsvToNumbers]

(

  @String AS VARCHAR(8000)

)

RETURNS

  @Numbers TABLE (Number INT)

AS

BEGIN

  SELECT @String =

    LTRIM(

      RTRIM(

        REPLACE(

          ISNULL(@String, ''), '  ' /* tab */, ' ')))

  IF (LEN(@String) = 0)

    RETURN

  DECLARE @StartIdx       INT

  DECLARE @NextIdx        INT

  DECLARE @TokenLength    INT

  DECLARE @Token          VARCHAR(16)

  SELECT  @StartIdx       = 0

  SELECT  @NextIdx        = 1

  WHILE @NextIdx > 0

  BEGIN

    SELECT @NextIdx = CHARINDEX(',', @String, @StartIdx + 1)

    SELECT @TokenLength =

      CASE WHEN @NextIdx > 0 THEN @NextIdx

      ELSE LEN(@String) + 1

    END - @StartIdx - 1

    SELECT @Token =

      LTRIM(

        RTRIM(

          SUBSTRING(@String, @StartIdx + 1, @TokenLength)))

    IF LEN(@Token) > 0

      INSERT

        @Numbers(Number)

      VALUES

        (CAST(@Token AS INT))

    SELECT @StartIdx = @NextIdx

  END

  RETURN

END

----From Table values to string

declare @RoleIds varchar(max)
select @RoleIds = coalesce(@RoleIds + ',','') + convert(varchar, nRoleID)
       from    EmpRole
       WHERE  sEmployeeID =10

ref:http://alekdavis.blogspot.com/2009/04/convert-string-to-table-in-sql.html