How to get part of string that matches with regular expression in SQL Server

Question

I am having a requirement where I need to parse a column value to get a work request number associated with it. For example, the column value may contain - "Worked on AB-0012589" or "AB-0012589 is completed" or whatever containing the work request number in it. (Here AB-0012589 is the work request number).

Format of the work request number will not change. It will be in xx-xxxxxxx format, where two characters before the hyphen will be letters and the later part will be numbers. How can I parse the work request number from the column value using regular expression?

So, if the column value is "Completed AC-0015587" my desired output is AC-0015587

Thanks in advance!

Answer 1

This works:

declare @Value nvarchar(4000)
set @Value='Worked a-232 on AB-0012589'

select substring(@Value,
patindex('%[A-Z][A-Z][-][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',@Value),10)

Replace @Value with your column

Output: AB-0012589