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

is the photo_note column always the same ? do you always want to start from The resolution ? — Manse
– Manse, Commented May 11, 2012 at 12:19
photo_note is more or less the same for all records except for the madnification value. Thanks for the help — Kim
– Kim, Commented May 11, 2012 at 12:25
For those who only need a specific string inside the data (not the rest of the data), this question may be more suitable. — Armfoot
– Armfoot, Commented Sep 30, 2015 at 16:47

Pablo · Accepted Answer · 2012-05-11 12:20:30Z

17

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.

edited May 11, 2012 at 12:20

answered May 11, 2012 at 12:18

Pablo

3,6732 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

select part of table column data in mysql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related