Changes to the Split function, which enables the splitting of all columns of the entire table through a stored procedure

/*
First, create a database
*/
if exists (select 1 from sys.sysdatabases where name= ' web_1 ')
Begin
Drop Database Web_1
End

Create DATABASE Web_1
On PRIMARY
(
NAME = Web,
Filename= ' E:\test\web.mdf ',--this path must exist in order to build the function
SIZE = 10,
MAXSIZE = UNLIMITED,
FileGrowth = 5
)
LOG on
(
Name= ' Web_dat ',
Filename= ' E:\test\web.ldf ',--this path must exist in order to build the function
SIZE =5MB,
MAXSIZE = 25MB,
FileGrowth =5MB
)
GO
/*
ii. Creating a test table that requires splitting of data
*/
Use Web_1

if exists (select 1 from sys.sysobjects where name = ' test ')--see if the table exists
Begin
DROP TABLE Test
End

CREATE TABLE Test
(
ID int identity (+),
Name varchar (255)
)
ALTER TABLE TEST ADD constraint pk_test_id primary key (ID)

INSERT into test (name) values (' aa,;bb,;cc,;dd ')
INSERT into test (name) VALUES (' 11,;22,;33,;444 ')

SELECT * FROM Test

/*
iii. Creating a type
*/
if exists (SELECT 1 from sys.types WHERE name = ' Locationtabletype ')--Verify that a type exists
Begin
Drop TYPE Locationtabletype
End

CREATE TYPE Locationtabletype as TABLE
(
Input nvarchar (max),
Separator nvarchar (max) default ', ',
Removeemptyentries bit default 1
);
/*
iv. Creating a stored procedure
*/
if exists (select 1 from sys.sysobjects where xtype = ' P ' and name= ' splitstring ')
Begin
drop procedure [splitstring]
End




Create Procedure [dbo]. [Splitstring]
(@TVP dbo. Locationtabletype readonly)--Incoming table
As
Begin
declare @OrignalInput nvarchar (max)
declare @Input nvarchar (max)
declare @Separator nvarchar (max)
DECLARE @RemoveEmptyEntries bit

DECLARE @rev table
(
Orignalinput nvarchar (max),
Dealname nvarchar (max)
)--Define the type of a table as the return table

DECLARE split_cursor Cursor for
Select Input,input,separator,removeemptyentries from @TVP-using cursors for Data loop splitting

Open Split_cursor
FETCH NEXT from Split_cursor to @OrignalInput, @Input, @Separator, @RemoveEmptyEntries--consistent with the query order above
While @ @FETCH_STATUS =0
Begin
--Actual processing of strings
declare @Index int, @Entry nvarchar (max)
Set @Index = charindex (@Separator, @Input)
while (@Index >0)
Begin
Set @Entry =ltrim (RTrim (substring (@Input, 1, @Index-1)))

if (@RemoveEmptyEntries =0) or (@RemoveEmptyEntries =1 and @Entry <> ")
Begin
Insert into @rev (orignalinput,dealname) Values (@OrignalInput, @Entry)
End

Set @Input = substring (@Input, @Index +datalength (@Separator)/2, Len (@Input))
Set @Index = charindex (@Separator, @Input)
End

Set @Entry =ltrim (RTrim (@Input))
if (@RemoveEmptyEntries =0) or (@RemoveEmptyEntries =1 and @Entry <> ")
Begin
Insert into @rev (orignalinput,dealname) Values (@OrignalInput, @Entry)
End

FETCH NEXT from Split_cursor to @OrignalInput, @Input, @Separator, @RemoveEmptyEntries
End
SELECT * from @rev-show results
Close Split_cursor--closing cursors
Deallocate Split_cursor--releasing resources
End

/*
v. Call a stored procedure implementation to split
*/

DECLARE @LocationTVP as Locationtabletype;

INSERT into @LocationTVP (Input, separator,removeemptyentries)
Select name, ',; ', 1 from test--inserts the data from the real table into the type

EXEC [splitstring] @LocationTVP

/*
Vi. Description

The return table here is not an entity table, if you need not only to split the content, you can change the table to the entity table, there is a data column before splitting, can be used to associate with the original table
*/

/*
vii. Delete Test
*/

drop procedure Splitstring

Drop TYPE Locationtabletype

DROP TABLE Test

Changes to the Split function, which enables the splitting of all columns of the entire table through a stored procedure