I'd definitely suggest you read the article that Bit Bucket pointed you to, but not until you understand how a Tally table actually replaces a loop in situations like this. Read the following article, first...http://www.sqlservercentral.com/articles/TSQL/62867/
Then, make a permanent Tally table...
--===== 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
FROM Master.dbo.SysColumns sc1,
--===== 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
After that, your problem suddenly becomes child's play, just like the article that Bit Bucket pointed you to...
--===== 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
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter,N,1) = '['
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs