-2

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

user18043574
  • 1
  • 1
  • Does this answer your question? [Query to get only numbers from a string](https://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string) – Patrick Jan 27 '22 at 03:57
  • If you can assure that the only numerics are the date, you can check the duplicate I just marked. – Patrick Jan 27 '22 at 03:57
  • `Like` is useful for pattern matching, e.g. in a `where` clause. If you want to pick a string apart based on a pattern you'll want to have a look at [`PatIndex`](https://learn.microsoft.com/en-us/sql/t-sql/functions/patindex-transact-sql?view=sql-server-ver15). It won't replace regex parsing, but it will let you find the match within a string. – HABO Jan 27 '22 at 04:14

2 Answers2

1

Building on HABO'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.

T N
  • 4,322
  • 1
  • 5
  • 18
-1

Did you try CHARINDEX?

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

CSY
  • 208
  • 1
  • 9
  • CHARINDEX does not support patterns. Given @str = 'wordword (2018)', the result is 'wor'. (A no match feeds a 0 into SUBSTRING – T N Jan 27 '22 at 13:49
  • Yes a no match would be a zero but then substring iirc starts with 1 and not 0 as index for the first char, no? – CSY Jan 28 '22 at 05:15
  • If that’s the case, perhaps a (case when end) statement with each when statement checking each numeric character and if it doesn’t return a 0, use substring on @str. Yes there would be 10 when statement with an else as default not found (11 total in that case statement) but that would solve the problem the OP is having, no? – CSY Jan 28 '22 at 05:18