Find_in_set feature of MySQL to find elements in an array or list of Values - Example and Diagrams

In an earlier post , we have seen how using group_concat() function we can group all the values of a column into comma separated array/list of values.

Please see the picture below:

Array elements distributed over 5 different rows and needs to be merged

On doing a group_concat(eligibility_file_staging_complete_id), we get the values in all the rows into a single row.

Example query:

select group_concat(eligibility_file_staging_complete_id)into @v_staging_id_list from temp_multiple_mc_complete;

select @v_staging_id_list;

Merge all columns into a single array using group_concat() function

Now, our requirement is to find each ID from another table and do some operation if the match is found.
We can use the FIND_IN_SET() function for this.

Example query:

  select efsc.eligibility_file_staging_complete_id as reference_id, efsc.* from
eligibility_file_staging_complete as efsc
where find_in_set(efsc.eligibility_file_staging_complete_id , @v_staging_id_list)
and file_id = v_file_id;

The above query will pick only those rows that are in the array @v_staging_id_list.

Please like us on Facebook or LinkedIn if you find our blog helpful.

Using Group_Concat() function in MySQL - Handy function to extract all values of a column

The Group_Concat() function in MySQL saved the day for me this week. So, a post thanking the authors. :)

I was loading some basic geographical data of Users who logged in to a website. The data looked as follows:

Row_id	Language	Name
42914	ES	Reader1
42915	ES	Reader1
44623	EN	Reader1
44624	EN	Reader1
44625	EN	Reader1

The dataset is actually pretty heavy with lots of other columns. So, my objective was to have a single row for 'NAME' with all the languages he uses in a delimited format. 

For example, the result output should become:

Name	Languages
Reader1	ES | EN
Reader2	EN | AR

And the group_concat() function in MySQL was just what I was looking for.

Example query:

select

 Name

, group_concat(distinct actor_languages separator '|')

 from tweet_data

group by Name

;

The query will group your dataset by Name, and create a pipe separated string of all the languages in the language column.

Please remember to use distinct. Else, we will have one entry for each row of Name, and this will become a really long string.

Suggestions or comments are welcome as always !!