MySQL string segmentation User-Defined Function

/*** Method 1 */select * from dbo. split ('01 _ 02000003', '_') error. The returned result is not the result we originally wanted: www.2cto.com ------------------- -- expected result 01 02 03 ------------------- www.2cto.com -- actual result: 01 _ 02 _ 03 I have written similar string segmentation UDF before, I have never thought about the problem above. My original FUNCTION is like this:/* StringToTable */create function StringToTable (@ StringX varchar (8000), @ Split nvarchar (10 )) RETURNS @ TableResult TABLE (TableID nvarchar (20) as begin declare @ Index int
SET @ Index = CHARINDEX (@ Split, @ StringX, 1) WHILE (@ Index> = 1) BEGIN www.2cto.com insert into @ TableResult select left (@ StringX, @ Index-1) SELECT @ StringX = RIGHT (@ StringX, LEN (@ StringX)-@ Index), @ Index = CHARINDEX (@ Split, @ StringX, 1) end if (@ StringX <> '') insert into @ TableResult SELECT @ StringX return end use a similar select * from dbo. split ('01 _ 02000003', '_') has the same problem. After the modification, the program is/* StringToTable */create function StringToTable (@ StringX varchar (8000), @ Split nvarchar (10 )) RETURNS @ TableResult TABLE (TableID nvarchar (20) as begin declare @ Index int DECLARE @ LenIndex int SELECT @ LenIndex = LEN (@ Split), @ Index = CHARINDEX (@ Split, @ StringX, 1) WHILE (@ Index> = 1)
Begin insert into @ TableResult select left (@ StringX, @ Index-1) SELECT @ StringX = RIGHT (@ StringX, LEN (@ StringX)-@ Index-@ LenIndex + 1 ), @ Index = CHARINDEX (@ Split, @ Stri www.2cto.com ngX, 1) end if (@ StringX <> '') insert into @ TableResult SELECT @ StringX return end/*** method 2 */set @ B = '2017; 123; 234; 567 '; create temporary table splittable (id INT AUTO_INCREMENT primary key, value varchar (20); www.2cto.com set @ SQL = concat ("insert into splittable (value) values ('", replace (@ B, ';', "'), ('"), "')"); prepare stem from @ SQL; execute stem; select * from splittable;