Search for a value in a numeric variable list

,

When you have a variable that hold a comma separated numbers like '1,2,3' and you have a table with a column that has the value that should match a number in that list, you can use this script to avoid building a string and then executing it.

You have to convert the columnID to varchar, the size of the varchar must be the biggest size that the number can have, for example if ColumnID range from 0 to 9999, the varchar will be varchar(4).

The list MUST NOT have spaceses between the numbers, if so, the script will not work.

DECLARE @InList varchar(30)

SET @InList = '1,2'

SELECT *
FROM #ColumnValues
WHERE (PATINDEX(RTRIM(cast(ColumnID as varchar(2))) + ',%',@InList)>0 		--First One
	or PATINDEX('%,' + RTRIM(cast(ColumnID  as varchar(2))) + ',%',@InList)>0 	--Middle
	OR PATINDEX('%,' + RTRIM(cast(ColumnID  as varchar(2))),@InList)>0		--last one
	OR RTRIM(cast(ColumnID  as varchar(2))) = @InList ) 				--just one in the list

Rate

Share

Share

Rate