T-SQL- How to extract pattern '(yyyy)' in string with where 'like' query-CodePudding

I have a string data like 'wordword (2018)', want to extract those data with pattern (yyyy). have tried with '%/([0-9][0-9][0-9][0-9]/)%' but doesn't work

CodePudding user response：

Building on Patrick's comment, you can use something like:

DECLARE @Pattern VARCHAR(50) = '%([0-9][0-9][0-9][0-9])%'
SELECT A.value, yyyy = SUBSTRING(A.value, NULLIF(PATINDEX(@pattern, A.Value), 0)   1, 4)
FROM (
    VALUES
        ('wordword (2018)'),
        ('Nothing here'),
        ('this (2010) and that (2020)')
) A(value)

SQL Server has a very limited pattern matching support, so I converted your regex to the closest thing that SQL Server supports. The NULLIF() in the above converts a not-found index of zero to a null, which propagates to the result.

CodePudding user response：

Did you try CHARINDEX?

SUBSTRING(@str, CHARINDEX(‘[0-9][0-9][0-9][0-9]’,@str),4)