How about this:
update table
set answer = concat_ws("," ,
if(left(columnA,1)="*",substring(columnA,2),""),
if(left(columnB,1)="*",substring(columnB,2),""),
if(left(columnC,1)="*",substring(columnC,2),"") )
Greg
> I've got a 700-question multiple choice quiz that I've got imported into
MySQL, with one small problem: the answers to the quiz are not a separate
column. I have three columns for each of the possible answers; the correct
answer is denoted by a "*" in the appropriate field. For example, if I have
columns A, B, and C for the three answers, they would contain data such as
"George Washington", "*Abraham Lincoln", and "Bill Clinton".
I'd like to be able to select out all the fields with a * in them, and then
place them in a new, blank column called Answer. However, I'm really not
sure how to select these out, since I can't say "where <colname> like "*%".
Further complicating the matter is the fact that, in a few of the questions,
there are multiple answers, and thus multiple columns with the asterisk in
them.
How would I go about selecting out these fields? Even if I can only get
those with one answer, I'd be immensley appreciative.
Thanks,
Alex Kirk
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
<
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
