Asked  7 Months ago    Answers:  5   Viewed   33 times

I've got a table field membername which contains both the last name and the first name of users. Is it possible to split those into 2 fields memberfirst, memberlast?

All the records have this format "Firstname Lastname" (without quotes and a space in between).

 Answers

60

Unfortunately MySQL does not feature a split string function. However you can create a user defined function for this, such as the one described in the following article:

  • MySQL Split String Function by Federico Cargnelutti

With that function:

DELIMITER $$

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

DELIMITER ;

you would be able to build your query as follows:

SELECT SPLIT_STR(membername, ' ', 1) as memberfirst,
       SPLIT_STR(membername, ' ', 2) as memberlast
FROM   users;

If you prefer not to use a user defined function and you do not mind the query to be a bit more verbose, you can also do the following:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(membername, ' ', 1), ' ', -1) as memberfirst,
       SUBSTRING_INDEX(SUBSTRING_INDEX(membername, ' ', 2), ' ', -1) as memberlast
FROM   users;
Tuesday, June 1, 2021
 
gMale
answered 7 Months ago
60

First split on "-", then split the third element returned on ","

<?PHP
$title = $row['title']; // or however you get the title.
$parts = explode("-",$title);
if ( count($parts) != 3) die("Invalid title: $title");
$newtitle = $parts[0];
$company = $parts[1];
$location_parts = explode(',',$parts[2]);
$city = $location_parts[0];
$state = $location_parts[1];

//now you've got $newtitle, $company, $city, and $state to do something productive with.
Wednesday, March 31, 2021
 
DiglettPotato
answered 9 Months ago
17

You can use a stored procedure which uses a cursor to solve this but it's not very elegant but neither is a comma separated list of writers !

Had the following code lying around from a similar question but you'd better check it thoroughly.

Hope it helps :)

mysql> select * from movies_unf;
+---------+-------------+------------------------------------------------------+
| movieID | movie_title | written_by                                           |
+---------+-------------+------------------------------------------------------+
|       1 | movie1      | person1, person2                                     |
|       2 | movie2      | person3                                              |
|       3 | movie3      | person4, person2, person6                            |
|       4 | movie4      | person4, person4, person1, person2, person1,person8, |
|       5 | movie1      | person1, person2                                     |
+---------+-------------+------------------------------------------------------+
5 rows in set (0.00 sec)

call normalise_movies_unf();

mysql> select * from movies;
+----------+--------+
| movie_id | title  |
+----------+--------+
|        1 | movie1 |
|        2 | movie2 |
|        3 | movie3 |
|        4 | movie4 |
+----------+--------+
4 rows in set (0.00 sec)

mysql> select * from writers;
+-----------+---------+
| writer_id | name    |
+-----------+---------+
|         1 | person1 |
|         2 | person2 |
|         3 | person3 |
|         4 | person4 |
|         6 | person6 |
|        12 | person8 |
+-----------+---------+
6 rows in set (0.00 sec)

mysql> select * from movie_writers;
+----------+-----------+
| movie_id | writer_id |
+----------+-----------+
|        1 |         1 |
|        1 |         2 |
|        2 |         3 |
|        3 |         2 |
|        3 |         4 |
|        3 |         6 |
|        4 |         1 |
|        4 |         2 |
|        4 |         4 |
|        4 |        12 |
+----------+-----------+
10 rows in set (0.00 sec)

Example tables

drop table if exists movies_unf;
create table movies_unf
(
movieID int unsigned not null primary key,
movie_title varchar(255) not null,
written_by varchar(1024) not null
)engine=innodb;

insert into movies_unf values 
(1,'movie1','person1, person2'),
(2,'movie2','person3'),
(3,'movie3','person4, person2, person6'),
(4,'movie4','person4, person4, person1, person2, person1,person8,'), -- dodgy writers
(5,'movie1','person1, person2'); -- dodgy movie

drop table if exists movies;
create table movies
(
movie_id int unsigned not null auto_increment primary key,
title varchar(255) unique not null
)engine=innodb;

drop table if exists writers;
create table writers
(
writer_id int unsigned not null auto_increment primary key,
name varchar(255) unique not null
)engine=innodb;

drop table if exists movie_writers;
create table movie_writers
(
movie_id int unsigned not null,
writer_id int unsigned not null,
primary key (movie_id, writer_id)
)engine=innodb;

Stored procedure

drop procedure if exists normalise_movies_unf;

delimiter #

create procedure normalise_movies_unf()
begin

declare v_movieID int unsigned default 0;
declare v_movie_title varchar(255);
declare v_writers varchar(1024);

declare v_movie_id int unsigned default 0;
declare v_writer_id int unsigned default 0;
declare v_name varchar(255);

declare v_csv_done tinyint unsigned default 0;
declare v_csv_idx int unsigned default 0;

declare v_done tinyint default 0;
declare v_cursor cursor for 
    select distinct movieID, movie_title, written_by from movies_unf;

declare continue handler for not found set v_done = 1;

start transaction;

open v_cursor;
repeat
  fetch v_cursor into v_movieID, v_movie_title, v_writers;

  set v_movie_title = trim(v_movie_title);
  set v_writers = replace(v_writers,' ', '');

  -- insert the movie
  insert ignore into movies (title) values (v_movie_title);
  select movie_id into v_movie_id from movies where title = v_movie_title;  

  -- split the out the writers and insert
  set v_csv_done = 0;       
  set v_csv_idx = 1;

  while not v_csv_done do
    set v_name = substring(v_writers, v_csv_idx, 
      if(locate(',', v_writers, v_csv_idx) > 0, 
        locate(',', v_writers, v_csv_idx) - v_csv_idx, 
        length(v_writers)));

      set v_name = trim(v_name);

      if length(v_name) > 0 then
        set v_csv_idx = v_csv_idx + length(v_name) + 1;

        insert ignore into writers (name) values (v_name);
        select writer_id into v_writer_id from writers where name = v_name; 
        insert ignore into movie_writers (movie_id, writer_id) values (v_movie_id, v_writer_id);
      else
        set v_csv_done = 1;
      end if;

  end while;

until v_done end repeat;
close v_cursor;

commit;

truncate table movies_unf;

end#

delimiter ;

EDIT

Amended sproc so that it doesnt skip key values !

drop procedure if exists normalise_movies_unf;

delimiter #

create procedure normalise_movies_unf()
begin

declare v_movieID int unsigned default 0;
declare v_movie_title varchar(255);
declare v_writers varchar(1024);

declare v_movie_id int unsigned default 0;
declare v_writer_id int unsigned default 0;
declare v_name varchar(255);

declare v_csv_done tinyint unsigned default 0;
declare v_csv_idx int unsigned default 0;

declare v_done tinyint default 0;
declare v_cursor cursor for 
    select distinct movieID, movie_title, written_by from movies_unf;

declare continue handler for not found set v_done = 1;

start transaction;

open v_cursor;
repeat
  fetch v_cursor into v_movieID, v_movie_title, v_writers;

  set v_movie_title = trim(v_movie_title);
  set v_writers = replace(v_writers,' ', '');

  -- insert the movie

  if not exists (select 1 from movies where title = v_movie_title) then
    insert ignore into movies (title) values (v_movie_title);
  end if;  
  select movie_id into v_movie_id from movies where title = v_movie_title;  

  -- split the out the writers and insert
  set v_csv_done = 0;       
  set v_csv_idx = 1;

  while not v_csv_done do
    set v_name = substring(v_writers, v_csv_idx, 
      if(locate(',', v_writers, v_csv_idx) > 0, 
        locate(',', v_writers, v_csv_idx) - v_csv_idx, 
        length(v_writers)));

      set v_name = trim(v_name);

      if length(v_name) > 0 then
        set v_csv_idx = v_csv_idx + length(v_name) + 1;


        if not exists (select 1 from writers where name = v_name) then
          insert ignore into writers (name) values (v_name);
        end if;
        select writer_id into v_writer_id from writers where name = v_name; 
        insert ignore into movie_writers (movie_id, writer_id) values (v_movie_id, v_writer_id);
      else
        set v_csv_done = 1;
      end if;

  end while;

until v_done end repeat;
close v_cursor;

commit;

truncate table movies_unf;

end#

delimiter ;
Monday, July 5, 2021
 
Yrtymd
answered 5 Months ago
96

Your database table implementation seems bad designed, however in your case what you need would be a reverse function of GROUP_CONCAT, but unfortunately it doesn't exist in MySQL.

You have two viable solutions :

  1. Change the way you store the data (allow duplicate on the product_id field and put multiple records with the same product_id for different category_id)
  2. Manipulate the query result from within your application (you mentioned PHP in your question), in this case you have to split the category_ids column values and assemble a result set by your own

There is also a third solution that i have found that is like a trick (using a temporary table and a stored procedure), first of all you have to declare this stored procedure :

DELIMITER $$
CREATE PROCEDURE csv_Explode( sSepar VARCHAR(255), saVal TEXT )
body:
BEGIN

  DROP TEMPORARY TABLE IF EXISTS csv_Explode;
  CREATE TEMPORARY TABLE lib_Explode(
    `pos` int unsigned NOT NULL auto_increment,
    `val` VARCHAR(255) NOT NULL,
    PRIMARY KEY  (`pos`)
  ) ENGINE=Memory COMMENT='Explode() results.';

  IF sSepar IS NULL OR saVal IS NULL THEN LEAVE body; END IF;

  SET @saTail = saVal;
  SET @iSeparLen = LENGTH( sSepar );

  create_layers:
  WHILE @saTail != '' DO

    # Get the next value
    SET @sHead = SUBSTRING_INDEX(@saTail, sSepar, 1);
    SET @saTail = SUBSTRING( @saTail, LENGTH(@sHead) + 1 + @iSeparLen );
    INSERT INTO lib_Explode SET val = @sHead;

  END WHILE;

END; $$
DELIMITER ;

Then you have to call the procedure passing the array in the column you want to explode :

CALL csv_explode(',', (SELECT category_ids FROM products WHERE product_id = 100));

After this you can show results in the temporary table in this way :

SELECT * FROM csv_explode;

And the result set will be :

+-----+-----+
| pos | val |
+-----+-----+
|   1 | 200 |
|   2 | 300 |
+-----+-----+

It could be a starting point for you ...

Saturday, August 7, 2021
 
Rocket
answered 4 Months ago
72

You can use cSplit from my "splitstackshape" package with the argument stripWhite = FALSE.

For example, if we wanted to split all the "Allele*" columns, we would do:

library(splitstackshape)
cSplit(mydf, grep("Allele", names(mydf)), "", stripWhite = FALSE)
#        SNP Geno AlleleA_1 AlleleA_2 AlleleB_1 AlleleB_2 AlleleC_1
# 1: marker1   G1         A         A         A         A         A
# 2: marker2   G1         T         T         T         T         T
# 3: marker3   G1         T         T         T         T         T
# 4: marker1   G2         C         C         C         C         C
# 5: marker2   G2         A         A         A         A         A
# 6: marker3   G2         T         T         T         T         T
# 7: marker1   G3         G         G         G         G         G
# 8: marker2   G3         A         A         A         A         A
# 9: marker3   G3         T         T         T         T         T
#    AlleleC_2 AlleleD_1 AlleleD_2 AlleleE_1 AlleleE_2
# 1:         A         A         A         A         A
# 2:         T         T         T         T         T
# 3:         T         T         T         T         T
# 4:         C         C         C         C         C
# 5:         A         A         A         A         A
# 6:         T         T         T         T         T
# 7:         G         G         G         G         G
# 8:         A         A         A         A         A
# 9:         T         T         T         T         T
Saturday, August 7, 2021
 
Bere
answered 4 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share