Avatar of whorsfall
whorsfall
Flag for Australia asked on

SQL Regex?

Hi,

I am trying to work out a SQL regex match for a field.

So lets say I have a field called data1 that contains:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.SystemGroupName = "AAA\\AdGroup1"

I want to be able to do a T-SQL select statement that will just extract the string AdGroup1
from the above expression.

So how can I get a capture group. So to use a regex like below and just get the capture group:
.*SMS_R_System.SystemGroupName.*\\(.*)\"

Thanks,

Ward.
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
Terry Woods

We need to confirm we understand the logic with which you want to use to extract the value.

Assuming your pattern describes pretty much what you want, it seems to be the value in the string between the two backslashes and the closing double quote, following the string "SMS_R_System.SystemGroupName".

I'm not a T-SQL guru, but I'm pretty sure you need a Regular Expressions add-on/plug-in before you can do them in T-SQL. If you don't want to go down that track, you could potentially use use some clever logic with CHARINDEX and SUBSTRING to extract the value.
whorsfall

ASKER
Hi,

Yes your understanding is correct. Would pat index work?
Terry Woods

With some accuracy, depending on your data. You'd perhaps look for an occurrence of \\ which is within x many characters of the occurrence of "SMS_R_System.SystemGroupName", then find the occurrence of the double quote at the end to work out how many characters to take as a substring.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Vitor Montalvão

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question