--===== Create and populate the Tally table on the fly
 SELECT TOP 11000 --equates to more than 30 years of dates
        IDENTITY(INT,1,1) AS N
   INTO dbo.Tally
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance
  ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally_N 
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Allow the general public to use it
  GRANT SELECT ON dbo.Tally TO PUBLIC

--===== Simulate a passed parameter
DECLARE @Parameter VARCHAR(8000) 
    SET @Parameter = 'John[Sam]Will[Joseph]Smith[Joe]' 

--===== Suppress the auto-display of rowcounts to keep them from being
    -- mistaken as part of the result set.
   SET NOCOUNT ON

--===== Get the items in the brackets and number them 
 SELECT ROW_NUMBER() OVER (ORDER BY N) AS ElementNumber,
        SUBSTRING(@Parameter,N+1,CHARINDEX(']',@Parameter,N+1)-N-1) AS ElementValue
   FROM dbo.Tally
  WHERE N < LEN(@Parameter)
    AND SUBSTRING(@Parameter,N,1) = '['