How to use a list of values in Excel as filter in a query
- by Luca Zavarella
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? You can find the script DBA_SplitCRLFList.sql here.
Bye!!
