Substing-从中间字符中获取日期

Question

我有一个大小可变的varchar列。该列中的值可能不同，例如

###LoggedIn 2021-03-30 16:09:10###
I have ###LoggedIn 2021-03-29 16:09:10### regularly
I am not sure if I ###LoggedIn 2021-03-28 16:09:10### 

我只想从字符串中取出Date。我尝试了Substring，但它只适用于第一种情况。，我想要一个适用于所有场景的脚本。不仅仅是这3个，还有更多。

select CAST(ISNULL(SUBSTRING('###LoggedIN 2021-03-04 16:09:10###', 13, 11),'1900-01-01')  AS date)

Answer 1

使用patindex查找字符串的已知部分。然后使用substring提取日期。

select
x.col1
-- Find the end of the date and substring it. Convert to date.
, convert(date, substring(y.col2, 1, patindex('%###%', y.col2)-1))
from (
values
('###LoggedIn 2021-03-30 16:09:10###')
, ('I have ###LoggedIn 2021-03-29 16:09:10### regularly')
, ('I am not sure if I ###LoggedIn 2021-03-28 16:09:10###')
) x (Col1)
-- Find the start of the date, and substring it, use cross apply so we can use this multiple times
cross apply (values (substring(x.col1, patindex('%###LoggedIn %', x.col1)+12, len(x.col1)))) y (col2);

Answer 2

您提供的数据遵循非常规则的模式。大概，您希望日期在'###LoggedIn '之后。这意味着你可以只寻找这个模式，走到最后，然后取19个字符：

select v.*,
substring(col, charindex('###LoggedIn ', col) + 12, 19)
from (values ('###LoggedIn 2021-03-30 16:09:10###'),
('I have ###LoggedIn 2021-03-29 16:09:10### regularly'),
('I am not sure if I ###LoggedIn 2021-03-28 16:09:10###')
) v (col);

Answer 3

您可以将子字符串与patindex一起使用

select convert(date,substring('###LoggedIN 2021-03-04 16:09:10###',patindex('%###LoggedIn %','###LoggedIN 2021-03-04 16:09:10###')+12,10)) 

来自表

select convert(date,substring(Column,patindex('%###LoggedIn %',Column)+12,10)) from YourTable