user7172167 - 4 months ago 34

SQL Question

i'm trying to write a regular expression that will convert the hours into minutes.

Currently I have a value which give me the number of hours before the dot and the number of minutes after the dot, for example 6.10 is 6 hours and 10 minute.

Can i use a regular expression to look for the value before the dot and multiply by 60 (to get minutes) and then add the value after the dot (which is already in minutes)?

So as per the example used before, it would do:

(6 * 60) + 10

Thank you!

Answer

Regular expressions is probably an overkill for such a simple string manipulation. All you need is INDEX function to get the location of the dot and SUBSTR function to get hours and minutes.

Dot position:

```
select INDEX([yourstring], '.')
```

Hours (before the dot):

```
select SUBSTR([yourstring], 1, INDEX([yourstring], '.') - 1)
```

Minutes (after the dot):

```
select SUBSTR([yourstring], INDEX([yourstring], '.') + 1)
```