MySQL string segmentation User-Defined Function

Source: Internet
Author: User


/*** 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;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.