How to use PATINDEX in SQL Server?

Question

In SQL Server 2012, I have a column which has long text data. Somewhere within the text, there is some text of the format

{epa_file_num} = {138410-81}

If it exists, I want to extract out 138410-81 as a column value. In regular JS regex, I would use something like this { *epa_file_num *} *= *{ *\d*-?\d* *} to match the column, and then maybe a capturing group to get the value.

But how can I get it in SQL Server 2012?

Thanks

SQL Server doesn't support reg-ex or capturing groups. PATINDEX basically gives you the ability to do very primitive pattern matching, more like the "LIKE" command. — pmbAustin, Jul 19 '19 at 21:39
Is there no way to use a combination of Pattindex, and substings to get the value? — omega, Jul 19 '19 at 21:40
I'd write a utility in some other language to read the value and do whatever complex string parsing you need... a C# utility that uses RegEx for example. Trying to hack something together into SQL that does what you want will be complicated, time-consuming, and error-prone. This just isn't what SQL is designed for. — pmbAustin, Jul 19 '19 at 21:44

score 0 · Answer 1 · answered Jul 20 '19 at 06:00

Not a regex, but this might do what you want:

DECLARE @Input VARCHAR(MAX)='{some name} = {a value} some text {epa_file_num} = {138410-81} other text'
SET @Input=REPLACE(@Input,' ','')
SET @Input=SUBSTRING(@Input,NULLIF(PATINDEX('%{epa_file_num}={%',@Input),0)+LEN('{epa_file_num}={'),LEN(@Input))
SET @Input=SUBSTRING(@Input,1,NULLIF(CHARINDEX('}',@Input),0)-1)
SELECT @Input

First, I remove all the spaces, then I look for {epa_file_num}= and take everything after this string, until the next }.

How to use PATINDEX in SQL Server?

1 Answers1