Arrays and SQL

An article that describes different techniques for faking arrays in SQL.

Faking Arrays in Transact SQL/

Here's a technique I wrote. It takes a delimited string and returns a results set. You can turn this into a function which returns a table variable too.

===========================================
create procedure usp_parse_string(@delimiter varchar(5), @parsestring varchar(1000))
as
SET NOCOUNT ON

declare @tempstring1 varchar(1000)
declare @tempstring2 varchar(1000)
declare @pos int, @oldpos int

create table #mytable (token_string varchar(100))

/* trim spaces of the string */
select @tempstring1 = ltrim(rtrim(@parsestring))
select @tempstring1 = REPLACE (@tempstring1, '"' , '' )
--select @tempstring1

/* see if delimiter is in string */
select @pos = charindex(@delimiter, @tempstring1)
/* select @tempstring2 */

/* if it is in the string, then get first token */
if @pos <> 0
begin
select @tempstring2 = substring(@tempstring1,1,@pos - 1)
--select @tempstring2
--insert it into the token table
insert into #mytable values (@tempstring2)
end

/* now get rest of tokens */
select @oldpos = @pos
select @pos = charindex(@delimiter, @tempstring1, @pos + 1)
while @pos <> 0
begin
select @tempstring2 = substring(@tempstring1, @oldpos + 1, @pos - @oldpos - 1)
insert into #mytable values (@tempstring2)
--select @tempstring2
select @oldpos = @pos
select @pos = charindex(@delimiter, @tempstring1, @pos + 1)
end

--get the last token
select @tempstring2 = substring(@tempstring1, @oldpos + 1, len(@tempstring1))
--select @tempstring2
insert into #mytable values (@tempstring2)

select * from #mytable

go
===========================================