By doing this with an identity Column on the row, you have a pseudo array for sql server. This user defined function will grab the nth occurance of a word in a string. By marrying that with the IDENTITY, it can be assumed to be an array. The next parts of these would be to be able to perform functions like DELETE and UPDATE of the word in that location (which may be more trouble than it's worth.) Pluse in Ken Henderson's Book, “The Guru's Guide to Stored Procedures, XML, and HTML”, has an entire chapter dedicated to “creating” arrays in sql server. You have to be extremely careful about memory management and object management. Suffice it to say, I don't have a box I can thouroughly destroy, so I never played with it.
But this concept is similar. At the very minium, this is an extension of all the “How do I parse words from a string” questions.
Anyway, here's the udf.
CREATE FUNCTION udf_GetWord (
@str nvarchar(4000)
, @Word int
, @Delim char(1)
)
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @LastPosition int, @DelimFound int, @Start int, @End int, @WordFound nvarchar(4000)
SELECT @LastPosition = 0, @DelimFound = 0
WHILE (@DelimFound < @Word-1)
BEGIN
IF (CHARINDEX(@Delim, @str, @LastPosition + 1) = 0)
BREAK
ELSE
BEGIN
SET @LastPosition = CHARINDEX(@Delim, @str, @LastPosition + 1)
SET @DelimFound = @DelimFound + 1
END
END
SET @Start = @LastPosition + 1
SET @End = CHARINDEX(@Delim, @str, @LastPosition + 1) - @Start
IF (@End = 0) SET @End = LEN(@str)
SELECT @WordFound = SUBSTRING(@str,@start,@end)
RETURN @WordFound
END