Parsing a delimeted string in T-SQL is fairly simple to do, but it does take a nice little chunk of code. I most commonly use this chunk of code when I need to pass an array from an application into a SQL Stored Procedure. The best way to implement this code into an application would be to create a SQL Function that you pass in a delimted string and it returns a table of values. This way you don't have to duplicate the code everytime you want to use it.
The following example parses the delimeted string and places each value into a variable of type Table. Once the values are placed into the table you can do anything you need to save/process these values.
--declare the list of Cities
DECLARE @CityList varchar(8000)
SET @CityList = 'Milwaukee|Chicago|New York|Seattle|San Francisco'
--declare the delimeter between each City
DECLARE @Delimeter char(1)
SET @Delimeter = '|'
--Parse the string and insert each city into the @tblCity table
DECLARE @tblCity TABLE(City varchar(50))
DECLARE @City varchar(50)
DECLARE @StartPos int, @Length int
WHILE LEN(@CityList) > 0
BEGIN
SET @StartPos = CHARINDEX(@Delimeter, @CityList)
IF @StartPos < 0 SET @StartPos = 0
SET @Length = LEN(@CityList) - @StartPos - 1
IF @Length < 0 SET @Length = 0
IF @StartPos > 0
BEGIN
SET @City = SUBSTRING(@CityList, 1, @StartPos - 1)
SET @CityList = SUBSTRING(@CityList, @StartPos + 1, LEN(@CityList) - @StartPos)
END
ELSE
BEGIN
SET @City = @CityList
SET @CityList = ''
END
INSERT @tblCity (City) VALUES(@City)
END
--Show all Cities in the @tblCity table
SELECT * FROM @tblCity