How to use a list of values in Excel as filter in a query

It often happens that a customer provides us with a list of items for which to extract certain information. Imagine, for example, that our clients wish to have the header information of the sales orders only for certain orders. Most likely he will give us a list of items in a column in Excel, or, less probably, a simple text file with the identification code:

   

As long as the given values ​​are at best a dozen, it costs us nothing to copy and paste those values ​​in our SSMS and place them in a WHERE clause, using the IN operator, making sure to include the quotes in the case of alphanumeric elements (the database sample is AdventureWorks2008R2):

SELECT *
FROM Sales.SalesOrderHeader AS SOH
WHERE SOH.SalesOrderNumber IN (
		'SO43667'
		,'SO43709'
		,'SO43726'
		,'SO43746'
		,'SO43782'
		,'SO43796')

Clearly, the need to add commas and quotes becomes an hassle when dealing with hundreds of items (which of course has happened to us!). It’d be comfortable to do a simple copy and paste, leaving the items as they are pasted, and make sure the query works fine.

We can have this commodity via a User Defined Function, that returns items in a table. Simply we’ll provide the function with an input string parameter containing the pasted items. I give you directly the T-SQL code, where comments are there to clarify what was written:

CREATE FUNCTION [dbo].[SplitCRLFList] (@List VARCHAR(MAX))
RETURNS @ParsedList TABLE (
	--<	Set the item length as your needs	 
	Item VARCHAR(255)
	)
AS
BEGIN
	DECLARE
		--<	Set the item length as your needs
		@Item VARCHAR(255)
		,@Pos BIGINT

	--< Trim TABs due to indentations
	SET @List = REPLACE(@List, CHAR(9), '')
	--< Trim leading and trailing spaces, then add a CR\LF at the end of the list
	SET @List = LTRIM(RTRIM(@List)) + CHAR(13) + CHAR(10)
	--< Set the position at the first CR/LF in the list
	SET @Pos = CHARINDEX(CHAR(13) + CHAR(10), @List, 1)

	--< If exist other chars other than CR/LFs in the list then...	
	IF REPLACE(@List, CHAR(13) + CHAR(10), '') <> ''
	BEGIN
		--< Loop while CR/LFs are over (not found = CHARINDEX returns 0)
		WHILE @Pos > 0
		BEGIN
			--< Get the heading list chars from the first char to the first CR/LF and trim spaces
			SET @Item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))

			--< If the so calulated item is not empty...
			IF @Item <> ''
			BEGIN
				--< ...insert it in the @ParsedList temporary table
				INSERT INTO @ParsedList (Item)
				VALUES (@Item)
					--(CAST(@Item AS int))	--< Use the appropriate conversion if needed
			END

			--< Remove the first item from the list...
			SET @List = RIGHT(@List, LEN(@List) - @Pos - 1)
			--< ...and set the position to the next CR/LF
			SET @Pos = CHARINDEX(CHAR(13) + CHAR(10), @List, 1)
				--< Repeat this block while the upon loop condition is verified
		END
	END

	RETURN
END

At this point, having created the UDF, our query is transformed trivially in:

SELECT *
FROM Sales.SalesOrderHeader AS SOH
WHERE SOH.SalesOrderNumber IN (
		SELECT Item
		FROM SplitCRLFList('SO43667
SO43709
SO43726
SO43746
SO43782
SO43796') AS SCL)

Convenient, isn’t it? SmileYou can find the script DBA_SplitCRLFList.sql here.

Bye!!

Comments

  1. Gravatar Frank Hell says:

    Re : # re: How to use a list of values in Excel as filter in a query

    WHILE loop?

    Ugly! Have you ever thought about set based operations? Common table expressions can help here:

    www.sommarskog.se/arrays-in-sql-2005.html#CTEs
  2. Gravatar lucazav says:

    Re : # re: How to use a list of values in Excel as filter in a query

    @Frank Hell

    Thank you for your suggestion.

    Be aware that you suggested a solution that uses a recursive CTE. Sometimes an iterative solution can be better then such rCTE solution. Take a look here:

    blogs.msdn.com/...

    By the way, as you can read in the article you suggested:

    "This method is not among the quickest; it beats the iterative method with a mere 15%"

    My post wants to be a "pragmatic" one and not a "theoretical" one. In fact, if you read carefully the article you suggested, in the "Final Words" section you can see:

    "On the other hand, in the very most situations, the plain iterative method will serve you very well."

    Regards.
  3. Gravatar Gil says:

    Re : # re: How to use a list of values in Excel as filter in a query

    I usually just select the list and hit control-H and replace \n with ',\n' using regular expression in the find/replace. A little cleanup on the first and last item, and all is well.
  4. Gravatar lucazav says:

    Re : # re: How to use a list of values in Excel as filter in a query

    Nice trick, Gil! Thank you for sharing that.
Comments have been closed on this topic.