Geek Answers Handbook

MySQL string separation by comma

I have String asdasdwdfef,rgrgtggt,weef , and I want output, as in table format, as shown below

 id decription 1 asdasdwdfef 2 rgrgtggt 3 weef

For this, I created a procedure. Here is my procedure.

 DELIMITER ;; CREATE Procedure Split(_RowData text, _Delimeter text) BEGIN DECLARE _Iterator INT default 1; DECLARE _FoundIndex INT; DECLARE _Data varchar(255); SET _FoundIndex = LOCATE(_Delimeter,_RowData); DROP TABLE IF EXISTS _RtnValue; CREATE temporary TABLE _RtnValue(ID INT AUTO_INCREMENT NOT NULL, description text, primary key(ID)); WHILE _FoundIndex > 1 DO INSERT INTO _RtnValue (description) SELECT _Data = LTRIM(RTRIM(SUBSTRING(_RowData, 1, _FoundIndex - 1))); set _RowData = SUBSTRING(_RowData, _FoundIndex + LENGTH(_Delimeter) / 2, LENGTH(_RowData)); SET _Iterator = _Iterator + 1; SET _FoundIndex = LOCATE(_Delimeter, _RowData); END WHILE; INSERT INTO _RtnValue(description) SELECT _Data = LTRIM(RTRIM(_RowData)); select * from _RtnValue; END

But when I execute it using the following command

 call Split('asdasdwdfef,rgrgtggt,weef', ',');

he gives me the following result:

 id decription 1 NULL 2 NULL 3 NULL

Please let me know how to fix this problem. I am using MySQL.

+5
source share
2 answers

I got an answer

First create a new function

 CREATE FUNCTION SPLIT_STR(x VARCHAR(255), delim VARCHAR(12), pos INT) RETURNS VARCHAR(255) RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos), LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1), delim, '');

Then create a stored procedure

 DELIMITER ;; CREATE PROCEDURE Split(in fullstr varchar(255)) BEGIN DECLARE a INT Default 0 ; DECLARE str VARCHAR(255); DROP TABLE IF EXISTS my_temp_table; CREATE temporary TABLE my_temp_table(ID INT AUTO_INCREMENT NOT NULL, description text, primary key(ID)); simple_loop: LOOP SET a=a+1; SET str=SPLIT_STR(fullstr,",",a); IF str='' THEN LEAVE simple_loop; END IF; #Do Inserts into temp table here with str going into the row insert into my_temp_table (description) values (str); END LOOP simple_loop; select * from my_temp_table; END

After that, when I call him call Split('asas,d,sddf,dfd'); He gives me the conclusion I want.

Thanx for every sentence.

+2
source

Requirements: divide the column with the separators into rows (table).

Why? You can use all the functions of SQL Aggregate to process strings.

Working sqlfiddle

Related questions that use this code:

I decided to use:

  • integer table (integersequence), which when "internally joined" or "cross-joined" (the same thing) to another table will generate rows that will undoubtedly have a unique serial number starting with 1. This is just used what RDBMS engines should do.

  • why is the integer table ?: It is small and will be loaded into the cache. This is easy to understand.

  • Instead of having a lot of code in the request, which hides what it actually does. I use functions that perform one task. It simplifies the basic request and can be checked and verified separately. At the moment, I am concerned about the ease of maintenance and understanding.

An example here is for one line. However, it can easily be expanded to a table of split rows of different sizes. "Cross join" will create all possible index parameters, starting with 1 when joining integersequence (I really need to use a different name: - /).

So the request is:

 SET @StrToParse = "asdasdwdfef,rgrgtggt,weef"; /* example */ select integerseries.id, count_in_set(@StrToParse, ','), value_in_set(@StrToParse, ',', integerseries.id) from integerseries where integerseries.id <= count_in_set(@StrToParse, ',');

Output:

 PositionOfString, CountOfCommaDelimitedStrings, StringAtThatPosition 1 3 asdasdwdfef 2 3 rgrgtggt 3 3 weef

Now, how do we get these values:

I decided to use two functions:

The names are associated with the mysql function 'FIND_IN_SET' .

1) COUNT_IN_SET function: returns the counter character delimited items in the column.

 CREATE FUNCTION `COUNT_IN_SET`(haystack VARCHAR(1024), delim CHAR(1) ) RETURNS INTEGER BEGIN RETURN CHAR_LENGTH(haystack) - CHAR_LENGTH( REPLACE(haystack, delim, '')) + 1; END$$

2) Function VALUE_IN_SET : treats the delimited list as one based array and returns the value in the specified "index".

 CREATE FUNCTION `VALUE_IN_SET`(haystack VARCHAR(1024), delim CHAR(1), which INTEGER ) RETURNS VARCHAR(255) CHARSET utf8 COLLATE utf8_unicode_ci BEGIN RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(haystack, delim, which), delim, -1); END$$

Intereseries table - see (Tally tables)

 CREATE TABLE `integerseries` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=500 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; /*Data for the table `integerseries` */ insert into `integerseries`(`id`) values (1); insert into `integerseries`(`id`) values (2); insert into `integerseries`(`id`) values (3); insert into `integerseries`(`id`) values (4); insert into `integerseries`(`id`) values (5); insert into `integerseries`(`id`) values (6); insert into `integerseries`(`id`) values (7); insert into `integerseries`(`id`) values (8); insert into `integerseries`(`id`) values (9); insert into `integerseries`(`id`) values (10);
+1
source

More articles:


All Articles