What I’m trying to ask is that I’ve a table Duration. It has only one field elapsed and it is VARCHAR. The table is this:
+-----------------+ | elapsed | +-----------------+ | 30 min | | 30 min | | 1 hour | | 1 hour | | 1 hour 30 min | | 1 hour 30 min | +-----------------+
I want this table:
+------------------------+ | elapsed |total | +------------------------+ | 30 min | 30 | | 30 min | 30 | | 1 hour | 60 | | 1 hour | 60 | | 1 hour 30 min | 90 | | 1 hour 30 min | 90 | +-----------------+------+
total should be integer or number. I’ve to perform some arithmetic on it later.
Here’s what I tried:
case WHEN substring_index(pa.Duration, 'hour', 1) > 0 and not substring_index(pa.Duration, 'min', 1) > 0 then cast(concat(pa.Duration,concat(' ' , 1)) as signed) * 60 WHEN substring_index(pa.Duration, 'hour', 1) > 0 and substring_index(pa.Duration, 'min', 1) > 0 then (cast(concat(pa.Duration,concat(' ' , 1)) as signed) * 60 + cast(concat(pa.Duration,concat(' ' , 3)) as signed) ) WHEN not substring_index(pa.Duration, 'hour', 1) > 0 and substring_index(pa.Duration, 'min', 1) > 0 then cast(concat(pa.Duration,concat(' ' , 1)) as signed) else 0 end as minutes
Please help me.
Note: I need MYSQL query not MSSQL.
Advertisement
Answer
If you only have min
and hour
, you can use:
select elapsed, ((case when elapsed like '% hour%' then substring_index(elapsed, ' hour', 1) * 60 else 0 end) + (case when elapsed like '%min%' then substring_index(substring_index(elapsed, ' min', 1), ' ', -1) + 0 else 0 end) ) as elapsed_minutes from t;
This extracts the number before “hour” if any, multiplies by 60 and adds in the number before “minute”, if any.
Here is a db<>fiddle.