Mysql Split Comma Separated String into Temp Table

MySQL Split Comma Separated String Into Temp Table

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 $$

Split comma separated varchar parameter up into temp table

use convert to XML and cross apply:

  DECLARE @str varchar(50)
  SET @str='John, Samantha, Bob, Tom'

  SELECT names = y.i.value('(./text())[1]', 'nvarchar(1000)')             
  FROM 
  ( 
    SELECT 
        n = CONVERT(XML, '<i>' 
            + REPLACE(@str, ',' , '</i><i>') 
            + '</i>')
  ) AS a 
  CROSS APPLY n.nodes('i') AS y(i)

OUTPUT:

names
-----
John
 Samantha
 Bob
 Tom

EDIT: it's not need to the temp table inside the proc so the proc will be:

CREATE PROCEDURE myProc

    (@nameList varchar(500))

AS
BEGIN

      SELECT names = y.i.value('(./text())[1]', 'nvarchar(1000)')             
      FROM 
      ( 
        SELECT 
            n = CONVERT(XML, '<i>' 
                + REPLACE(@nameList, ',' , '</i><i>') 
                + '</i>')
      ) AS a 
      CROSS APPLY n.nodes('i') AS y(i)
END

but if you want to insert it into a temp table, below is a the sample:

create table #names 
    (
        Name varchar(20)
    )

  DECLARE @str varchar(50)
  SET @str='John, Samantha, Bob, Tom'

  insert into #names
  SELECT names = y.i.value('(./text())[1]', 'nvarchar(1000)')             
  FROM 
  ( 
    SELECT 
        n = CONVERT(XML, '<i>' 
            + REPLACE(@str, ',' , '</i><i>') 
            + '</i>')
  ) AS a 
  CROSS APPLY n.nodes('i') AS y(i)

  select * from #names 
  drop table #names 

EDIT 2: the input string may contains some special characters like '<' , '>' , etc it's not standard for names but if the the given string contains them you can remove them by using replace function : replace(@str,'<','')

Split comma separated string into rows in mysql

Use a subquery of arbitrary digits to split your string.Instead of vals you can use '1,2,3'.

SELECT
  DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(vals, ',', n.digit+1), ',', -1) val
FROM
  tt1
  INNER JOIN
  (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) n
  ON LENGTH(REPLACE(vals, ',' , '')) <= LENGTH(vals)-n.digit;

See it working

splitting comma separated list into a temp table

select country, name, rank from table person_details

The query is syntactically incorrect. you don't need the keyword TABLE. Just do:

select country, name, rank from person_details

Strictly speaking, your table is not normalized. You should not store multiple values in a single column. Sooner or later you will see the performance issues. It is never too late to re-design your tables and store the values in separate columns.

Having said that, there are many ways to split comma delimited string into rows. here is one simple way using REGEXP_SUBSTR and INSTR in CONNECT BY clause:

SQL> WITH DATA AS(
  2  select q'['india','USA','australia']' countries,
  3  q'['tom', 'jill', 'jack']' names,
  4  q'['first', 'third', 'fifth']' ranks
  5  from dual
  6  )
  7  SELECT regexp_substr(translate(countries,'''',' '), '[^,]+', 1, LEVEL) countries,
  8  trim(regexp_substr(translate(names,'''',' '), '[^,]+', 1, LEVEL)) names,
  9  trim(regexp_substr(translate(ranks,'''',' '), '[^,]+', 1, LEVEL)) ranks
 10  FROM DATA
 11  CONNECT BY instr(countries, ',', 1, LEVEL - 1) > 0
 12  /

COUNTRIES                 NAMES                 RANKS
------------------------- --------------------- -------------------------
 india                    tom                   first
 USA                      jill                  third
 australia                jack                  fifth

SQL>

I have demonstrated other ways in my article ORACLE DELIMITED STRING MANIPULATION.

Breaking a stored procedure string into temp table mysql

You've found a decent example for doing this (if I do say so myself), although in principle, SQL is something of an awkward place to do it. Splitting a string and doing inserts is easier in a procedural language with easy regex support, like Perl. But sometimes it makes sense to do things in the database.

The important difference in what you are doing and what this code was written to accomplish is that you need to do a splittin operation twice -- nested. After splitting the key/value pairs, you then need to separate the key from the list of values and then use more splitting for each set of comma-separated values.

Since _value now contains hat=blue,yellow, you can further split the key from the values list, with SUBSTRING_INDEX(_value,'=',1) for the key and SUBSTRING_INDEX(_value,'=',-1) for the values list.

You are stuck where you are, because you still need to change that comma-separated list of values into something iteratable, to do the inserts... so you could modify this code pretty extensively... or you could just call a second copy of this code, in a second procedure, modified to accept the extracted key and values list, split the values list on commas, and do the inserts. The second copy of the procedure will not create the temp table, since it already exists.

Also, since the first copy of the procedure is not looking for , but rather for :=:, you'll need to modify it to remove the correct number of characters as it walks the string.

Change this...

  SET _list = INSERT(_list,1,_nextlen + 1,'');

...to this...

  SET _list = INSERT(_list,1,_nextlen + 3,'');

...because your delimiter is 3 characters long, rather than 1, as in the original example, and that's what this line does -- removes the value you just inserted, and the delimiter following it.

Break down a delimited string into a temporary table

To insert the result of the function to a temporary table, you should use:

INSERT INTO #tempTable(data)
SELECT data
FROM dbo.Split(@Params, '|') s
WHERE ID = 1

Using WHERE ID = 1, however, only inserts the first item in the delimited list. If you want to filter then number of values to be inserted in the table use <=.

For example, you only want to insert the first three values, then you use:

WHERE ID <= 3

Additionally, your function to split is slower compared to some other available functions. I recommend that you a Tally-based splitter such as Jeff Moden's DelimitedSplit8K.

Split comma separated string and insert into new table with corresponding PK

try this:

Select t.Id,f.SplitData AS Value from #MyTable t
CROSS APPLY dbo.fnSplitString([Values],',') f

---

Related Topics