从字符串中提取年份

Question

是否可以从随机字符串中提取年份?例如"randomtext20221108randomtext">

它之前或之后的文本都不相同，但日期格式为"20221108"它是。

Answer 1

我想这个查询可能对你有帮助，

DECLARE @yourText varchar(100) = 'randomtext20221108randomtext'
DECLARE @begin int = PATINDEX('%[0-9][0-9][0-9][0-9]%', @yourText)
DECLARE @end int = @begin + 7
SELECT SUBSTRING(@yourText, @begin, @end - @begin + 1)

如果你只需要年份，你可以这样做，

SELECT SUBSTRING('randomtext20221108randomtext', PATINDEX('%[0-9][0-9][0-9][0-9]%', 'randomtext20221108randomtext') , 4)

Answer 2

也许下面的例子对你有用，假设日期都是本世纪

select s, Substring(s, p, 4) [Year]
from (
values('kdfjgkj44kjf420221108dfkgjhdfiuh45rt'),('qwerty20210409'),('nodate 2345 here')
)t(s)
cross apply(values(NullIf(PatIndex('%2[0-9][0-9][0-9][0-9][1-9][0-9][1-9]%', s),0)))p(p);

Answer 3

create table #randomTexts (col1 nvarchar(max));
insert into #randomTexts values ('randomtext20221108randomtext');
insert into #randomTexts values ('2022asdasd20211108randomtext');
insert into #randomTexts values ('1234567820181108randomtext');
insert into #randomTexts values ('xxx20331332zzz20221108randomtext');
WITH CTE_Numbers AS 
(
SELECT MAX(LEN(col1))-1 as n FROM #randomTexts
UNION ALL 
SELECT n-1 FROM CTE_Numbers
WHERE n>0
)
SELECT col1,CAST(SUBSTRING(col1,n,8) AS DATE) AS validDate, SUBSTRING(col1,n,4) AS year
FROM #randomTexts
CROSS JOIN CTE_Numbers
WHERE TRY_CAST(SUBSTRING(col1,n,8) AS DATE) IS NOT NULL
AND n+8<= LEN(col1);