T-SQL: Parse a delimited string

Feb 3, 2006 • Database

Parsing a delimited 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 delimited string and it returns a table of values. This way you don’t have to duplicate the code every time you want to use it.

The following example parses the delimited 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