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

Cleaning up your phone numbers is a hard task that is not well-suited for SQL. This would be hard, even with regular expression matching. — Gordon Linoff
– Gordon Linoff, Commented Feb 13, 2017 at 15:01
To add to what Gordon said, SQL does not support regular expressions. There is some support for limited pattern matching, but SQL is not the right tool for the task you are trying to accomplish. — pymaxion
– pymaxion, Commented Feb 13, 2017 at 15:03
This data is such a mess that it is going to be incredibly challenging in any language to clean this up automatically. — Sean Lange
– Sean Lange, Commented Feb 13, 2017 at 15:06
Not sure how many rows you have in your actual table but I would venture to say that it would likely take more time to write up automated solution than it would to just fix the data manually. And in this case a manual solution is far more likely to get the results correct. :) — Sean Lange
– Sean Lange, Commented Feb 13, 2017 at 15:09

John Cappelletti · Accepted Answer · 2017-02-13 15:25:10Z

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

answered Feb 13, 2017 at 15:25

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

John Cappelletti Over a year ago

@RohiniMathur Happy it helped

Rohini Mathur Over a year ago

Hi John, the suggested solution is working awesome, is it possible to bypass this error which i am getting when tested against production database. "Invalid length parameter passed to the LEFT or SUBSTRING function." Please help

John Cappelletti Over a year ago

@RohiniMathur Give me a moment to take a peek at it

John Cappelletti Over a year ago

@RohiniMathur Having trouble creating an error. I inserted bogus values into your sample data ... NULL, {blank}, aaa, and 99 any insight as to the type of string causing the error?

Rohini Mathur Over a year ago

Give me a moment please.

|

Collectives™ on Stack Overflow

Critical Regex Implementation in SQL Server

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related