Extracting the times when the data looks like this - 'Monday|7:00-17:00' in SQL?-CodePudding

Is there a way to extract the starting time and the ending time of this type of text data in SQL? 'Monday|7:00-17:00' I need to calculate the difference and get the number of hours.

I tried SUBSTR but since the days have different lengths MONDAY, TUESDAY, etc., it doesn't work.

CodePudding user response：

You can use a mix of SUBSTR and INSTR functions to retrieve the hours like this:

SELECT SUBSTR('Monday|7:00-17:00', INSTR('Monday|7:00-17:00','|') 1, 100);

CodePudding user response：

Take a peek at this example: https://dbfiddle.uk/HyMsdfoJ

select dt
  , instr(dt, '|')
  , substr(dt, instr(dt, '|')   1, 100)
  , instr(substr(dt, instr(dt, '|')   1, 100), '-')
  , substr(
      substr(dt, instr(dt, '|')   1, 100),
      1,
      instr(substr(dt, instr(dt, '|')   1, 100), '-') - 1
    ) as starttime
  , substr(
      substr(dt, instr(dt, '|')   1, 100),
      instr(substr(dt, instr(dt, '|')   1, 100), '-')   1,
      100
    ) as endtime
  from (select 'Saturday|9:00-11:00' as dt) t

You can change select 'Saturday|9:00-11:00' as dt to select 'Mon|17:00-18:00' as dt and try it again.

In the query above, I break down the problem by:

finding the position where | exists. If the text was Mon|17:00-18:00, that'd be position 4
extracting the text after | using substr, which would be 17:00-18:00
finding the position of - in 17:00-18:00, which would be 6
putting learnings from the above 3 items to get the start time
- I take the text after | (17:00-18:00) and extract from position 1 to position 5
- That gives me 17:00
repeat a similar technique to get end time
- I take the text after - in 17:00-18:00 by extracting 100 characters after position 7
- 100 is an arbitrary number I chose. You can get more sophisticated with a bit of math and extract the exact length

Here's another example with a table: http://sqlfiddle.com/#!5/4f347/2/0