Luca Zavarella Blog

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!!

Legacy Comments


Gil
2012-06-23
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.

lucazav
2012-06-26
re: How to use a list of values in Excel as filter in a query
Nice trick, Gil! Thank you for sharing that.