MySQL string segmentation UDF _ MySQL

MySQL string segmentation user-defined function bitsCN.com
/*** Method 1 */select * from dbo. split ('01 _ 02000003 ',' _ ') error. the returned result is not the result we originally wanted: ------------------- -- expected result 01 02 03 ----------------- -- actual result: 01 _ 02 _ 03 I have written a similar string segmentation UDF before, and 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 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 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); set @ SQL = concat ("insert into splittable (value) values ('", replace (@ B, ';', "'), ('"), "')"); prepare stem from @ SQL; execute stem; select * from splittable; bitsCN.com