Data Cleanup - SQL Server

Asked

Viewed 93 times

4

I’m working with a column of phones, but some lines have two phones separated by ";".

99999999 ; 99999222
12345678 ; 87654321
40028922
123123456 ; 1122334455

My goal is to put duplicate phones on other separate lines.

99999999
99999222
12345678
87654321
40028922
123123456
1122334455

Would have some way to accomplish this with SQL?

  • What version of SQL Server??

1 answer

5


If the SQL Server version is 2016 (or newer), try:

-- código #1
SELECT E.ID, rtrim (ltrim (SS.[value])) as Telefone
  from dbo.tbEmpresa as E 
       cross apply String_Split (E.Telefones, ';') as SS;

However, if it is before 2016, here is another solution:

-- código #2
SELECT E.ID, 
       ltrim (rtrim (SS.Item)) as Telefone
  from dbo.tbEmpresa as E 
       cross apply dbo.DelimitedSplit8K (E.Telefones, ';') as SS;

The code of the user function Delimitedsplit8k, authored by Jeff Moden, is at the end of the article "Separating multi-valued text content (split string)”.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.