Contact Us

Use the form on the right to contact us.

You can edit the text in this area, and change where the contact form on the right submits to, by entering edit mode using the modes on the bottom right. 


Oak Ridge, TN, 37830
United States

Sql-Sangria

Parse List Into Table

Wade Cantley

The Parse Quencher

  • 3 oz gin
  • 2 oz St. Germain elderflower liqueur
  • Juice of 1 lime
  • 3 sprigs basil, plus more to garnish
  • 1 cucumber
  • Tonic
  • Ice

The idea here is that you want to parse a list, in this example a list of numbers, and put them into a table using a simple "while" loop.  Here is how you might do it.

-- The idea is to parse a passed delimited list into a table

-- This is the variable holding the list
declare @CategoriesList VARCHAR(MAX)
SET		@CategoriesList = '1,2,3,4'



-- Create tables to hold the data being passed
CREATE TABLE #ttbCategories (
	categoryId INT
)

-- Populate the categories Table
	SET @CategoriesList = LTRIM(RTRIM(@CategoriesList))+ ','
	SET @Pos = CHARINDEX(',', @CategoriesList, 1)

	IF REPLACE(@CategoriesList, ',', '') <> ''
	BEGIN
		WHILE @Pos > 0
		BEGIN
			SET @OrderID = LTRIM(RTRIM(LEFT(@CategoriesList, @Pos - 1)))
			IF @OrderID <> ''
			BEGIN
				INSERT INTO #ttbCategories (categoryId)
				VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
			END
			SET @CategoriesList = RIGHT(@CategoriesList, LEN(@CategoriesList) - @Pos)
			SET @Pos = CHARINDEX(',', @CategoriesList, 1)

		END
	END

-- Now, take a look at what you created
SELECT * FROM #ttbCategories
DROP TABLE #ttbCategories