Give comma separated strings as input to sql "IN" clause

Question

Possible Duplicate:
creating comma seperated string to be given as input to sql “IN” clause

HI,

i have to implement multiple select dropdown,and the selected values shud be formatted to be input to "IN" clause of sql.

Am storing the selected values in a string.But there is no delimiter between values ,so i cannot split the string.Are there any other methods for the string formatting.

If you store all values in a String without a delimiter you can not distinguish the values! Consider that also a blank (' ') is a delimiter - so you might have one without notice it :-) - Maybe post an example of your String. — FrVaBe, Jan 06 '11 at 10:19
@K. Claszen - this is true in general but there are solutions for special case. See my answer below. — Andreas Dolk, Jan 06 '11 at 10:38

score 1 · Answer 1 · answered Jan 06 '11 at 10:11

1

Fun solution: Add the values to ArrayList or LinkedList, the call toString(). And replace '['->'(', replace ']'->')'.

answered Jan 06 '11 at 10:11

卢声远 Shengyuan Lu

31,208
22
85
130

Or since the input is probably an array, you can directly call Arrays.toString( yourArray ), and then perform the same replacements. – Costi Ciudatu Jan 06 '11 at 10:16
1

Or since the input is probably an array, he can use this array to assemble the SQL `IN` clause String directly. – Andreas Dolk Jan 06 '11 at 10:45

score 0 · Answer 2 · answered Jan 06 '11 at 10:11

0

If you had a collection of strings and then copied them to 1 string without delimiters you cannot separate them again. Just do not do this. If you still have problems please send more details about your task.

answered Jan 06 '11 at 10:11

AlexR

114,158
16
130
208

Andreas Dolk · Answer 3 · 2011-01-06T10:46:49.663

If you know the values of the dropdown and all values are unique and not subsets of each other, then you can use String#contains() or regular expressions to test, which values have been selected.

But it's by far easier to simply add some trivial delimiter (like the common ";") while concatenating the String that holds the selection.

Example for the contains approach

 String[] legalValues = {"YES","NO","MAYBE"};
 String result = getSelection();  // returns a String like "MAYBEYES"
 StringBuilder inClauseBuilder = new StringBuilder();
 boolean isFirst = true;
 for (String legalValue:legalValues) {
   if (!result.contains(legalValue)
     continue;

   if (isFirst) {
     isFirst = false;
   } else {
     inClauseBuilder.append(",");
   }
   inClauseBuilder.append("\"").append(legalValue).append("\"");
 }
 String inClause = inClauseBuilder.toString();

Note - this approach will fail as soon as you have legal values like

String[] legalValues = {"YES","NO","MAYBE-YES", "MAYBE-NO"};
                         ^^^   ^^         ^^^          ^^

Give comma separated strings as input to sql "IN" clause

3 Answers3