SQlite - Substr

Question

In Sqlite, I have a column with dates that comes as follows: Hours (VARCHAR) Friday|18:00-2:00

in my code I have the following, my idea is to extract the text from |, since it would give me a range of hours:

select count(distinct b.id), h.business_id, substr(h.hours, '|')
    , b.stars

However, I still have the same result substr(h.hours, '|') Friday | 18:00-2:00

I need you to just extract from the above: 18:00-2:00

Thank you so much!

Answer 1

You can try this:

SELECT
    COUNT(DISTINCT b.id),
    h.business_id,
    TRIM(SUBSTR(h.hours, INSTR(h.hours, '|') + 1)) AS extracted_hours,
    b.stars
FROM
    first_table_name AS h
JOIN
    second_table_name AS b ON h.business_id = b.id;