Skip to main content

Splitting Comma-Separated Values In MySQL

CREATE TABLE numbers (n INT);
 INSERT INTO numbers VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),

 

 

https://cdn.sisense.com/wp-content/uploads/image-01-comma-blog.png

select 
  id, 
  substring_index(
    substring_index(email_recipients, ',', n), 
    ',', 
    -1
  ) as email
from dashboards
join numbers
  on char_length(email_recipients) 
    - char_length(replace(email_recipients, ',', '')) 
    >= n - 1
 
https://cdn.sisense.com/wp-content/uploads/image-03-comma-blog.png 


Reference Site : https://www.sisense.com/blog/splitting-comma-separated-values-in-mysql/

Comments

Post a Comment

Popular posts from this blog

Custom Sorting Array List in PHP

 <?php $order = array("Z","Srilanka","34", "Canada", "India", "USA"); $array = array(     array('id' => 7867867, 'title' => 'USA'),     array('id' => 3452342, 'title' => 'India'),     array('id' => 1231233, 'title' => 'Srilanka'),     array('id' => 5867867, 'title' => 'Z'), ); usort($array, function ($a, $b) use ($order) {     $pos_a = array_search($a['title'], $order);     $pos_b = array_search($b['title'], $order);     return $pos_a - $pos_b; }); print_r($array); ?>
Post a Comment
Read more

simple recursive function to copy entire directories

<?php function recurse_copy ( $src , $dst ) {     $dir = opendir ( $src );     @ mkdir ( $dst );     while( false !== ( $file = readdir ( $dir )) ) {         if (( $file != '.' ) && ( $file != '..' )) {             if ( is_dir ( $src . '/' . $file ) ) {                 recurse_copy ( $src . '/' . $file , $dst . '/' . $file );             }             else {                 copy ( $src . '/' . $file , $dst . '/' . $file );             }         }     }     closedir ( $dir ); } ?>
Post a Comment
Read more