T-Sql, Split string in the column and insert into table [duplicate]

Question

I have table like this

Name   |  Email             | Phone number 
alis     [email protected]        +989355555;+989366666;+9803777777
John     [email protected]        +989122222
sara     [email protected]        +989113212;+989113312

and I want a query to select this table like this and after that insert this table in other table.

Name   |  Email             | Phone number 
alis     [email protected]        +989355555
alis     [email protected]        +989366666
alis     [email protected]        +9803777777
John     [email protected]        +989122222
sara     [email protected]        +989113212
sara     [email protected]        +989113312

split all phone number and save them with similar fields name.

sql server 2016 introduced a built in string splitting function. If you are working with a lower version (and most of us are), read Aaron Bertrand's article about string splitting functions. — Zohar Peled
– Zohar Peled, Commented Jun 29, 2016 at 8:00

gotqn · Accepted Answer · 2016-06-29 08:02:52Z

2

You can try this (using only T-SQL):

DECLARE @DataSource TABLE
(
    [Name] VARCHAR(12)
   ,[Email] VARCHAR(12)
   ,[PhoneNumber] VARCHAR(1024)
);

INSERT INTO @DataSource ([Name], [Email], [PhoneNumber])
VALUES ('alis', '[email protected]', '+989355555;+989366666;+9803777777')
      ,('John', '[email protected]', '+989122222')
      ,('sara', '[email protected]', '+989113212;+989113312');

SELECT DS1.[Name]
      ,DS1.[Email]
      ,DS3.[value]
FROM @DataSource DS1
CROSS APPLY
(
    SELECT CAST(('<X>'+REPLACE(DS1.[PhoneNumber] ,';' ,'</X><X>')+'</X>') AS XML)
) DS2 ([Col])
CROSS APPLY
(
    SELECT T.C.value('.', 'varchar(32)') as value 
    FROM DS2.Col.nodes('X') as T(C)
) DS3 ([value]);

answered Jun 29, 2016 at 8:02

gotqn

44k47 gold badges167 silver badges257 bronze badges

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

Collectives™ on Stack Overflow

T-Sql, Split string in the column and insert into table [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related