ih4fgUjh

1-- Select your database
2USE sample_database;
3/* You might need to run this if MySQL says that the below function is non-determininstic (although I've added DETERMINSTIC keyword in it) */
4-- SET GLOBAL log_bin_trust_function_creators = 1;
5DELIMITER $$
6DROP FUNCTION IF EXISTS splitString $$
7-- Function splitString that returns a substring based on a certain delimiter's position
8CREATE FUNCTION splitString (        
9        x VARCHAR(255),
10        delim VARCHAR(12),
11        pos INT
12        )
13        RETURNS VARCHAR(255) DETERMINISTIC
14        RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
15         LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
16         delim, '') $$
17DROP PROCEDURE IF EXISTS getAllStrings $$
18-- Procedure to get all the words from the input string passed to it as argument
19CREATE PROCEDURE getAllStrings(IN idString varchar(256)) 
20    BEGIN
21		CREATE TEMPORARY TABLE IF NOT EXISTS tbl_temp_sep_strings( SPLIT_VALUES VARCHAR(256));
22		SELECT LENGTH(idString) - LENGTH(REPLACE(idString, ', ', '')) INTO @noOfCommas;
23			   IF  @noOfCommas = 0 
24			  THEN
25					INSERT INTO tbl_temp_sep_strings(SPLIT_VALUES) values(idString);
26			  ELSE 
27                    SET @y = 1;
28					WHILE @y  < @noOfCommas DO 
29					   SELECT splitString(idString, ', ', @y) INTO @tempSeparate; 
30					   SET  @y = @y + 1; 
31                       INSERT INTO tbl_temp_sep_strings(SPLIT_VALUES) values(@tempSeparate);
32					END WHILE; 
33		END IF; 
34END $$
35CALL getAllStrings('ab, ac'); -- Call the function like this
36/* Now, you have the result, i.e., all the split words in the column SPLIT_VALUES of the tbl_temp_sep_strings
37   which you can use by doing a SELECT action. This is helpful as it can be used in SQL IN clauses and other
38   similar places where you might be looking for a tuple of values.
39                          
40   Please note that the table is temporary which is good because then it will get deallocated when the connection
41   is closed or the session ends as we wouldn't want a table in our db to persist. You can remove the TEMPORARY keyword
42   otherwise.
43*/
44-- SELECT * FROM sample_database.tbl_temp_sep_strings
45                                             
46-- BONUS: If a field in some table has mixed values as in single as well as mixed, comma-separated values then you can use the below procedure
47DROP PROCEDURE IF EXISTS getIndividualSkills $$
48CREATE PROCEDURE cleanMixedUpColumn()
49	BEGIN
50		SELECT COUNT(DISTINCT infected_column) INTO @noOfSkills FROM sample_database.sample_table WHERE Skill_id IS NOT NULL;
51        SET @y1 = 0;
52        WHILE @y1  < @noOfSkills DO
53					   SET @sql1 = CONCAT('SELECT DISTINCT infected_column INTO @currentValue FROM sample_database.sample_table WHERE infected_column IS NOT NULL ORDER BY infected_column LIMIT 1 OFFSET ' ,@y1,';');
54             PREPARE stmt1 FROM @sql1;
55					   EXECUTE stmt1;
56             DEALLOCATE PREPARE stmt1;
57             CALL getAllStrings(@currentValue);
58					   SET  @y1 = @y1 + 1;
59		END WHILE; 
60END $$
61DELIMITER ;
62CALL cleanMixedUpColumn();