How to split single row into multiple columns in mysql

Question

Just ask if there is any function available in mysql to split elements of one row into several columns? I have a table row with fields, user_id, user_name, user_location.

In this user can add multiple locations. I hack places and save them in the table as a single row using php.

When I display user entries in a grid, I have a problem with pagination, since I display entries separating user_locations. Therefore, I need to split user_locations (one row into several columns).

Is there any function available in mysql to split and count records by character (%).

For example, user_location has US%UK%JAPAN%CANADA

How can I split this entry into 4 columns. I need to also check the count (4) values. thanks in advance.

Answer 1

First, normalize the line by deleting the empty space and making sure that there is% at the end:

 select replace(concat(user_location,'%'),'%%','%') as str from YourTable where user_id = 1 

Then we can count the number of entries with a trick. Replace “%” with “%” and count the number of spaces added to the string. For instance:

 select length(replace(str, '%', '% ')) - length(str) as LocationCount from ( select replace(concat(user_location,'%'),'%%','%') as str from YourTable where user_id = 1 ) normalized 

Using substring_index, we can add columns for multiple locations:

 select length(replace(str, '%', '% ')) - length(str) as LocationCount , substring_index(substring_index(str,'%',1),'%',-1) as Loc1 , substring_index(substring_index(str,'%',2),'%',-1) as Loc2 , substring_index(substring_index(str,'%',3),'%',-1) as Loc3 from ( select replace(concat(user_location,'%'),'%%','%') as str from YourTable where user_id = 1 ) normalized 

In your example, US%UK%JAPAN%CANADA this prints:

 LocationCount Loc1 Loc2 Loc3 4 US UK JAPAN 

So you see that this can be done, but parsing strings is not a SQL strength.

Answer 2

The “right thing” will split the locations into another table and establish many-to-many relationships between them.

 create table users ( id int not null auto_increment primary key, name varchar(64) ) create table locations ( id int not null auto_increment primary key, name varchar(64) ) create table users_locations ( id int not null auto_increment primary key, user_id int not null, location_id int not null, unique index user_location_unique_together (user_id, location_id) ) 

Then provide referential integrity using either foreign keys (and the InnoDB engine) or triggers.

Answer 3

it should do it

 DELIMITER $$ DROP PROCEDURE IF EXISTS `CSV2LST`$$ CREATE DEFINER=`root`@`%` PROCEDURE `CSV2LST`(IN csv_ TEXT) BEGIN SET @s=CONCAT('select \"',REPLACE(csv_,',','\" union select \"'),'\";'); PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt; END$$ DELIMITER ; 

Answer 4

You must do this in your client application, not in the database.

When you make an SQL query, you must statically indicate the columns that you want to receive, that is, you indicate the DB with the columns that you want in your result set before executing. For example, if you have a date-time, you can do something like select month(birthday), select year(birthday) from ... , so in this case we will divide the column’s birthday into 2 other columns, but in the query indicates which columns we will have.

In your case, you will need to get exactly this string US%UK%JAPAN%CANADA from the database, and then you will split it later in your software, i.e.

 /* get data from database */ /* ... */ $user_location = ... /* extract the field from the resultset */ $user_locations = explode("%", $user_location); 

Answer 5

This is a bad design. If you can change it, save the data in two tables:

 table users: id, name, surname ... table users_location: user_id (fk), location 

users_location will have a foreign key for the user userughid user.