Get only numbers or 1 defualt value in MYSQL-CodePudding

I'm using the following information from the db, numbers and text. The text will always be at the end of the value.

| Formato  |
| -------- |
| 1L       |
| 4L       |
| 5L       |
| 20L      |
| 60L      |
| 210L     |
| 100ml    |
| 250ml    |
| 300ml    |
| 400ml    |
| 500ml    |
| 110grs   |
| 400grs   |
| 500grs   |
| 5kg      |
| 20kg     |
| 180kg    |

So, I'm trying to make a substring on MYSQL when:

a) if the value has a L, get just numbers (20L -> 20)
b) if the value has a ml or grs, get 1 (250 ml -> 1 ; 110grs -> 1)
c) if the value has Kg, get just numbers (180Kg -> 180)

in MYSQL

SELECT CASE(Formato) WHEN

case a;
case b;
case c;

CodePudding user response：

In your case you can try:

select Formato,case when Formato like '%ml' or Formato like '%grs' then 1 else cast(Formato as UNSIGNED) end as result from yourtable

And for better performance i suppose u store the value of Formato and unit in two columns.