A Stored Procedure interview question

A Stored Procedure interview question
Users1

Name company company_address url1
Joe ABC work Lane abc.com; XYZ.com
Jill XYZ job street abc.com; XYZ.com
Write the stored procedure to create a new table
Users2
Name company company_address url1

Joe ABC work Lane abc.com
Joe ABC work Lane XYZ.com
Jill XYZ job street abc.com
Jill XYZ job street XYZ.com
========================================================== ========================================================== ======================================
Drop table users1
Go
Create Table users1 (name varchar (10), company varchar (10), company_address varchar (20), url1 varchar (20 ))
Insert into users1
Select 'job', 'abc', 'work Lane ', 'abc. com; XYZ.com'
Union all select 'jill', 'xyz', 'job Street ', 'abc. com; XYZ.com'

Drop proc up_test
Go
Create proc up_test
As
If exists (select 1 from sysobjects where type = 'U' and name = 'users2 ')
Drop table users2
Select * into users2
From (
Select name, company, company_address, left (url1, charindex (';', url1)-1) as url1
From users1
Union all
Select name, company, company_address, right (url1, Len (url1)-charindex (';', url1 ))
From users1) T
Order by name, company, company_address
Go
Exec up_test

Select * From users2
/*
Name company company_address url1
------------------------------------------------------------
Jill XYZ job street abc.com
Jill XYZ job street XYZ.com
Joe ABC work Lane XYZ.com
Joe ABC work Lane abc.com

(The number of affected rows is 4)
*/

========================================================== ========================================================== ======================================

-- The primary function should be used, and then the root worker; split the Primary Worker

go
-- function
Create Function splitlist
(@ separator char (1), @ list varchar (8000 ))
Returns @ returntable table (listitem varchar (1000) Collate database_default)
as
begin
declare @ index int
declare @ newtext varchar (8000)
If @ list = NULL
return
set @ Index = charindex (@ separator, @ list)
while not (@ Index = 0)
begin
set @ newtext = rtrim (ltrim (left (@ list, @ index-1)
set @ list = right (@ list, len (@ list)-@ index)
insert into @ returntable (listitem) values (@ newtext)
set @ Index = charindex (@ separator, @ List)
end
insert into @ returntable (listitem) values (rtrim (ltrim (@ list )))
return
end

Go
-- Maximum number of bytes
Create Table users1 (name varchar (10), company varchar (20), company_address varchar (20), url1 varchar (100 ))
Insert into users1 select 'job', 'abc', 'work Lane ', 'abc. com; XYZ.com'
Insert into users1 select 'jill', 'xyz', 'job Street ', 'abc. com; XYZ.com'

Create Table users2 (name varchar (10), company varchar (20), company_address varchar (20), url1 varchar (100 ))
Go
-- Stored progress

Create procedure DBO. usp_test
As

Declare @ name varchar (10), @ company varchar (20), @ company_address varchar (20), @ url1 varchar (100)
Declare C1 cursor
Select * From users1
Open C1
Fetch next from C1 into @ name, @ company, @ company_address, @ url1
While @ fetch_status = 0
Begin
Insert into users2
Select @ name, @ company, @ company_address, listitem from DBO. splitlist (';', @ url1) T
Fetch next from C1 into @ name, @ company, @ company_address, @ url1
End
Close C1
Deallocate C1

Go

Exec DBO. usp_test
Select * From users2
/*
Name company company_address url1
Joe ABC work Lane abc.com
Joe ABC work Lane XYZ.com
Jill XYZ job street abc.com
Jill XYZ job street XYZ.com

*/

Drop table users1, users2
Drop proc usp_test
Drop function splitlist