sql - 从文本中获取日期时间

Question

如何从字符串中分离并仅显示日期时间？

我有一列保存这些数据:

Comments
-----------------------------------------
Daniel reached the school by 9:25:00 alone
Breakfast was served by 10:17:27 in the morning
Reached back home late evening by 7:00:48

我使用这个查询来分离时间:

(CONVERT(varchar(max), Comments, 108)) [Comments],

它返回相同的行。字符串没有被过滤

当我尝试使用这个语句时:

(CONVERT(TIME, Comments, 108)) [Comments],

我遇到了这个错误

Conversion failed when converting date and/or time from character string.

我怎样才能克服时间并获得低于预期的结果？

Comments
----------------------------------------
9:25:00 
10:17:27 
7:00:48

Answer 1

使用 patindex 您可以找到时间模式。

select
try_cast(substring(Comments, patindex('%[ 0-9][0-9]:[0-9][0-9]:[0-9][0-9]%', Comments), 8) as time(0)) CommentTime
from your_table

CommentTime
09:25:00
10:17:27
07:00:48