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
This site is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to Amazon.com. We also participates in affiliate programs with Udemy, and other sites. This site is compensated for referring traffic and business to these companies.