Selecting only part of a field

[scialom]

Hello all

I have a field in a table that has this string:
"question: what is the time now?
answer: it is 9:00";

I would like to create a select statement that will select only the question part up till answer.

in other words the results should be:
field = "question: what is the time now?"

is it possible using sql statement?
thanks!

[Derokorian]

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index

SELECT SUBSTR_INDEX(field,'?',1) FROM table

[scialom]

thanks but when I try this I get:
1305 - FUNCTION xx.SUBSTR_INDEX does not exist
I google this problem and it seems like a space problem but I can't see extra space
any idea?

[Derokorian]

My bad I tried to combine 2 functions is SUBSTRING_INDEX not SUBSTR_INDEX >.< Sorry

[dagon]

above all true, but it sounds like you should be using 2 columns in the first place, one for the question and one for the answer. basic normalisation each cell should contain a single discrete piece of information

[johanafm]

And once you put the question and the answer in their separate columns, the need to keep metadata "Question:" and "Answer" stored with your data disappears as well.