mysql - 如何在 SQL 查询中分割特定字符后的字符串

Question

我有一个这样的查询

SELECT wp.id,
   w.course_id,
   wt.slug,
   wp.post_title,
   w.course_id,
   w.start_date,
   wp.post_name,
   w.end_date,
   w.duration,
   w.timings,
   w.timezone,
   w.reg_link
FROM       wp_posts wp
INNER JOIN wp_terms wt
INNER JOIN wp_term_relationships tr ON wt.term_id = tr.term_taxonomy_id
INNER JOIN wp_term_taxonomy wtt     ON wt.term_id=wtt.term_id
    AND wtt.term_taxonomy_id=tr.term_taxonomy_id
    AND tr.object_id=wp.id
JOIN wp_event_list w 
    ON trim(wp.post_title) = trim(
       substring(w.course_id, position('-' IN w.course_id)+1,LENGTH (w.course_id)))
WHERE w.end_date >= CURRENT_DATE
    AND wp.post_type ='event'
    AND wtt.taxonomy = 'EventCategory'
GROUP BY w.id
ORDER BY w.start_date, w.timings ASC

在这个 w.timings 查询中，我有一个类似“8:30 AM - 10:00 AM”的值。我想显示 w.timings 列值，例如“8:30 AM”。如何在上面的查询中拆分并显示该字符串？

我使用的是 MySQL 5.6 版本。

Answer 1

使用SUBSTRING_INDEX(str,delim,count)

它返回 # 分隔符之前的子字符串。

SUBSTRING_INDEX(w.timings, ' - ', 1) as timings

这是一个dbfiddle演示