Critical Regex Implementation in SQL Server

Question

I am not sure if this can be done in SQL Server. May someone please help me if possible with SQL?

I have a table with data looks like this:

DECLARE @MYTABLE TABLE
(
    ColumnA VARCHAR (50)
)

INSERT INTO @MYTABLE
    SELECT '01268 591558 * 3 Marina' UNION ALL
    SELECT '01322 421980    JULIE  A/P' UNION ALL
    SELECT '01296620096 BR TAKES CARD EOM' UNION ALL
    SELECT '07547 740863/07986 019852' UNION ALL
    SELECT '07754553528 - TIM' UNION ALL
    SELECT '07732418595 sam' UNION ALL
    SELECT '01793 425574- Charlotte' UNION ALL
    SELECT '01268 591558 * 3 Marina' UNION ALL
    SELECT '07967 967404 CELIA' UNION ALL
    SELECT '0208 361 1213 / 1433      /8899 ' UNION ALL   
    SELECT '0208 361 1213 / 1433 '  UNION ALL        
    SELECT '0208 361 1213 / 1433'  UNION ALL         
    SELECT '01206 578671 / 564272' UNION ALL
    SELECT '01206735561/07748116152' UNION ALL
    SELECT '0208 361 1213 / 1433  '  UNION ALL       
    SELECT '01234 754047   SUE' UNION ALL
    SELECT '01206735561/07748116152/0156589'

and I am expecting output like

May someone please help me with this?

Thanks

Answer 1

Works with the sample data, but there are some potential pitfalls.

Currently the XML will split up to 9 positions (easy to expand or contract).

Clearly you could add more ColumnX if needed. Just follow the pattern

Example

Select A.*
      ,Column1 = left(Pos1,11)
      ,Column2 = case when Try_Convert(float,Pos2) is null then null else Left(Pos1,11-Len(Pos2))+Pos2 end
      ,Column3 = case when Try_Convert(float,Pos3) is null then null else Left(Pos1,11-Len(Pos3))+Pos3 end
From @MYTABLE A
Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                      ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
                      ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(replace(A.ColumnA,' ',''),'/','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as C
            ) B

Returns