Extract year from string-CodePudding

Is it possible to extract the year from a random string? For example "randomtext20221108randomtext"

The text before or after it's never the same, but the date format "20221108" it is.

CodePudding user response：

I think this query may help you,

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)

and if you need only year, you can do,

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

CodePudding user response：

Perhaps the following example might work for you, makes the assumption dates are all this century

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);

CodePudding user response：

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);