Regular Expressions in SQL Server

Question

I have a table with list of users as below.

christopher.j.sansom
vinay.prabhakar
Guillaume.de.Miribel (Stage 2B); jean-marie.pierron (Stage 3B)
ian.notley; pavan.sethi
Ron.M.Barbeau
jason.angelos 
jonathan.l.lange, ramesh.t.murti,
nicole.f.cohen

Can we get the records as below. Need to return comma separated records as new rows.

christopher.j.sansom
vinay.prabhakar
Guillaume.de.Miribel
jean-marie.pierron 
ian.notle
pavan.sethi
Ron.M.Barbeau
jason.angelos 
jonathan.l.lange
ramesh.t.murti
nicole.f.cohen

Answer 1

To do that you need a string splitter query/function.
This is an example, there are other way to do it.

With Normalize AS (
  SELECT REPLACE(CONCAT(REPLACE(names, ',', ';'), ';'), ';;', ';') Names
  FROM   Table1
), Splitter AS (
  Select names String
       , WordCounter = 0
       , NWordStart = 1
       , NWordEnd = CHARINDEX(';', names)
       , Word = CAST('' as nvarchar(255))
       , WordNumber = LEN(names) - LEN(REPLACE(names, ';', '')) + 1
  FROM   Normalize
  UNION ALL
  SELECT s.String
       , WordCounter = s.WordCounter + 1
       , NWordStart = s.NWordEnd + 1
       , NWordEnd = COALESCE(NULLIF(CHARINDEX(';', s.String, NWordEnd + 1), 0)
                           , LEN(s.String) + 1)
       , Word = LTRIM(Cast(SubString(String, s.NWordStart, s.NWordEnd - s.NWordStart) 
                           AS nvarchar(255)))
       , WordNumber = s.WordNumber
  FROM   Splitter s
  WHERE  s.WordCounter + 1 <= s.WordNumber
)
SELECT LEFT(WORD , CHARINDEX(' ', CONCAT(Word, ' ')) - 1) Word
FROM   Splitter
WHERE  Word <> '';

SQLFiddle Demo

The CTE Normalize change all the separator char to ; to have a single separator for the split.
The CTE Splitter split the string into chunk using the ; as the separator.
The main query remove the stage information searching for the space between the name and the left bracket.

Answer 2

See Regex here: https://regex101.com/r/hD2mQ8/1

You can use this pattern:

/(^[\w.-]+)|(?<=; |, )[\w.-]+/ with global and multi-line modifiers to capture the text that you need, but I'm not sure how you would return each one to a new line without seeing your current code.