Data Cleanup - SQL Server

Question

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?

Answer 1

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)”.