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 blog post is licensed under the Creative Commons Attribution 3.0 United States License, unless explicitly stated otherwise within the blog post content. All other content on this website is not licensed under Creative Commons licensing.