Using Regex in SQL Server, I couldn't solve the following problem.

Question

Hello all,

I have strings like that

OPEN SYSTEMS SUB GR (GM/BTIB(1111)/BTITDBL(2222)/BTVY(4444)/ACSVTYSAG)

and I need to extract 2222 from it.

What I was doing is this on the GROUPS String:

    SUBSTRING(GROUPS, CHARINDEX('(',GROUPS, CHARINDEX('(',GROUPS, CHARINDEX('(',GROUPS,0)+1)+1)+1, 4 ) AS GroupNo

However I see that it is not very efficient and I ve been told to do it using the regex. I couldn't solve it. I hope if anyone can help me with it. Thanks.

Answer 1

SQL Server does not natively support regular expressions.

You can, however, use CLR integration to add a .NET stored procedure which can use regular expressions. See this article for a nice explanation.

Answer 2

Okay I understand now what you need everyting starting in the 3rd parentheses and then 4 digits long?

take a look at

Declare @Data varchar(8000)

select @Data='OPEN SYSTEMS SUB GR (GM/BTIB(1111)/BTITDBL(2222)/BTVY(4444)/ACSVTYSAG)'

select left(parsename(left(replace(@data,'(','.'),
    len(@data) - PATINDEX('%(%',reverse(@data))),1),4)