select part of table column data in mysql

Question

I have a table 'photo' which contains 2000 entries. That table has a column called photo_note which contains data in the format below but with different magnification value.

Magnification 30x. The resolution varies depending on.....

I need to select the rest of the column data starting with 'The resolution' and append it in another field 'photo_note_2'

Any suggestion how to do this in mysql is most appreciated

Answer 1

SUBSTRING lets you return part of a string. INSTR returns the position of a string in other string.

If you know that all the columns will have 'The resolution', then:

SELECT SUBSTRING(photo_note, INSTR(photo_note, 'The resolution')) from table;

If you know that all will have a 'x. ' before the string you want to retrieve, then

SELECT SUBSTRING(photo_note, INSTR(photo_note, 'x. ') + 3) from table;

You can see all the string functions for mysql here.