Best way to do WHERE IN on multi Uni selector value

Stephen Rushing asked on April 16, 2015 00:57

I have a Uni selector field and want to find the best way to check if a particular ID is in the column.

I found a Func_Selection_ParseIDs function in the database, but Uni selector stores its values with a ";" delimeter, as opposed to ",", which the function requires...Here's what I have so far, which works, but I'm not convinced its the preferred way. I could do a mess of LIKE clauses, but that seems even worse. Any ideas?

... WHERE 555 IN (select ItemID from Func_Selection_ParseIDs(replace(MyUniselectorColumn, ';', ',')))

uniselector sql query

Recent Answers

Brenden Kehren answered on April 16, 2015 15:17

A sql function or a macro would work best. I'm not aware of a macro that already exists but it should be pretty simple to create.

0 votesVote for this answer Mark as a Correct answer

Charles Matvchuk answered on April 16, 2015 19:39

Use a sql function in your select clause which will give you the smallest footprint. If you need assistance in writing it just post complete example and objects you are working with.

0 votesVote for this answer Mark as a Correct answer

Stephen Rushing answered on April 16, 2015 20:19

I've created a macro and am using it in the WHERE condition of a control, but the quotes of my sub query keep getting doubled.

SELECT [ItemID] FROM Func_Selection_ParseIDs(REPLACE(MyUniselectorColumn, '';'', '',''))

My code looks like this, but I've also tried building it as a plain string -- same result.

//Replace clause for delimeter, if not a comma
string toParse = delimeter == "," ? column : "REPLACE(" + column + ", '" + delimeter + "', ',')";

//Create the query
var query = new DataQuery();
query.Column("ItemID");
query.From("Func_Selection_ParseIDs(" + toParse + ")");
return query;

If I write the same query directly in the WHERE field, it works fine. Not sure what's causing those double quotes or how to avoid them.

0 votesVote for this answer Mark as a Correct answer

Joshua Adams answered on April 16, 2015 22:38

At the end of your macro place this: |(handlesqlinjection)false

That is what I have had to do in the past.

2 votesVote for this answer Mark as a Correct answer