I have a column as:

| Size |
| --- |
| 75cl |
| 70im |
| 7im |
| 30cim |
| 30clim |

And I need to convert the text to numbers but conditionally:

| Size |
| --- |
| 75cl |
| 70 |
| 7 |
| 30 |
| 30cl |

Where the size column includes "im" then I just want to return the numbers.

I have tried to write a few queries like:

```
SELECT DISTINCT(LTRIM(RTRIM(size))) from tbl_size;

```

To determine how many rows I need to cleanse but I am a bit stuck on conditionally displaying the rows:

```
SELECT CASE WHEN size = '70im' THEN '70' etc

```

But I have 3,000 distinct values to accomodate and I am a bit stuck as to the best way to do this.  

Any advice is appreciated, I am using MySQL.