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

Accepted Answer

`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.

select part of table column data in mysql

Solution

Problem