MySQL Split Comma Separated String Into Temp Table

by

This is pretty much the same question as Can Mysql Split a column?

MySQL doesn’t have a split string function so you have to do work arounds. You can do anything with the data once you split it using one of the methods listed on the answer page above.

You can loop over that custom function and break when it returns empty, you’ll have to play and learn some syntax (or at least I would) but the syntax for a FOR loop in mysql is here:
http://www.roseindia.net/sql/mysql-example/for.shtml

You can iterate over it, incrementing the position in the function below:

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

(Credit: https://blog.fedecarg.com/2009/02/22/mysql-split-string-function/ )

Which should return ” if no match is found, so break the loop if no match is found. This will allow you to with only mysql parse over the split string and run the insert queries into a temp table. But man why not just use a scripting language like php for that kind of work? 🙁

Code for loop syntax:

DELIMITER $$  

CREATE PROCEDURE ABC(fullstr)

   BEGIN
      DECLARE a INT Default 0 ;
      DECLARE str VARCHAR(255);
      simple_loop: LOOP
         SET a=a+1;
         SET str=SPLIT_STR(fullstr,"|",a);
         IF str="" THEN
            LEAVE simple_loop;
         END IF;
         #Do Inserts into temp table here with str going into the row
         insert into my_temp_table values (str);
   END LOOP simple_loop;
END $$

More Related Contents:

  1. How to do a regular expression replace in MySQL?
  2. MySQL strip non-numeric characters to compare
  3. How to extract two consecutive digits from a text field in MySQL?
  4. How to extract the nth word and count word occurrences in a MySQL string?
  5. How to remove all non-alpha numeric characters from a string in MySQL?
  6. How to count words in MySQL / regular expression replacer?
  7. #1139 – Got error ‘repetition-operator operand invalid’ from regexp
  8. Does MySQL Regexp support Unicode matching
  9. How do I refer to capture groups in a MySQL regex?
  10. MySQL SELECT LIKE or REGEXP to match multiple words in one record
  11. Is it Possible to Enforce Data Checking in MySQL using Regular expression
  12. How to regex in a MySQL query
  13. remove all numeric characters from column mysql
  14. mySQL regex in the where clause
  15. How to use IN & NOT IN same query [closed]
  16. MySQL: Large VARCHAR vs. TEXT?
  17. MySQL: is a SELECT statement case sensitive?
  18. Check overlap of date ranges in MySQL
  19. Mysql: Select rows from a table that are not in another
  20. add column to mysql table if it does not exist
  21. Querying multiple databases at once
  22. Sorting varchar field numerically in MySQL
  23. MySQL: When is Flush Privileges in MySQL really needed?
  24. MySQL: update a field only if condition is met
  25. Split a string and loop through values in MySQL stored procedure
  26. Create an index on a huge MySQL production table without table locking
  27. MySQL concurrency, how does it work and do I need to handle it in my application
  28. How can I “select *” from a table in MySQL but omit certain columns?
  29. For homebrew mysql installs, where’s my.cnf?
  30. Force MySQL to return duplicates from WHERE IN clause without using JOIN/UNION?

Leave a Comment