convert regex to string in sql server

Question

convert regex to string in sql server

Helpseeker

Right there with Babe

Points: 782
More actions
November 15, 2019 at 1:10 pm

#3698015

Hi Am using sql server management studio 2014. Am struck in getting the below expected output.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

May I ask why you need to do this? Seems like an unusual requirement.

I haven't tested this, but I think that the output is also of limited use (because the stuff in brackets is generally mandatory, so the output would not match the expression).

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Helpseeker Right there with Babe Points: 782 More actions · Answer 2

we got this data from a sharepoint source and need to get the specific value from column A regular expression..

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

It's not easy! You might have to use a proper REGEX parser to get this working properly.

But it may help others if you take the time to provide your data in the form of CREATE TABLE / INSERT scripts.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 4

Why have you posted this in SQL 2017 when it's for SQL 2014? What do you mean by getting struck? Did you mean stuck? What are you trying to do? Is it just converting the "Reg ex value" string into the output string? If so, have you tried using the REPLACE function, something like this?

SELECT T.C, 
       REPLACE(
       REPLACE(
       REPLACE(
       REPLACE(
       REPLACE(T.C,'[A-Z0-9]+','')
               ,'|',',')
               ,'(?i)^?=','')
               ,')[A-Z0-9]{1,15}$','')
               ,'^(?=','')
  FROM (values ('(?i)^?=CAFS[A-Z0-9]+|CNAC[A-Z0-9]+|C528[A-Z0-9]+)[A-Z0-9]{1,15}$'),
               ('^(?=C624[A-Z0-9]+|C624[A-Z0-9]+)[A-Z0-9]{1,15}$')
       ) T(C)

Phil Parkin SSC Guru Points: 246985 More actions · Answer 5

I took this from https://regex101.com/:

2019-11-15_09-11-19

The point being that the string which you are extracting does not even match the expression from which it was extracted. How can this be of use?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel