0

Thanks to this answer https://stackoverflow.com/a/8180159/16349298 , i'm able to translate a string into a temporary table (usable for WHERE <id> IN <tmpTable>.<colomn>)

The only modification i made is at the end (The select) :

CREATE PROCEDURE stringToTmpTable(IN inputString VARCHAR(255), IN sep VARCHAR(255))
BEGIN
declare pos int;           -- Keeping track of the next item's position
declare item varchar(100); -- A single item of the input
declare breaker int;       -- Safeguard for while loop 
  -- The string must end with the delimiter
  if right(inputString, 1) <> sep then
     set inputString = concat(inputString, sep);
  end if;
DROP TABLE IF EXISTS MyTemporaryTable;
CREATE TEMPORARY TABLE MyTemporaryTable ( columnName varchar(100) );
  set breaker = 0;

  while (breaker < 2000) && (length(inputString) > 1) do
     -- Iterate looking for the delimiter, add rows to temporary table.
     set breaker = breaker + 1;
     set pos = INSTR(inputString, sep);
     set item = LEFT(inputString, pos - 1);
     set inputString = substring(inputString, pos + 1);
     insert into MyTemporaryTable values(item);
  end while;
SELECT * FROM MyTemporaryTable;
END

I would like to use this process in a function or procedure in order to call it in any procedure that needs it.

So here is the problem : I don't know how to store the result of this procedure into a variable : i can't use the SELECT * INTO @p FROM ...; like CALL stringToTmpTable(<string>,<separator>) INTO @table;

An other way would be to add OUT parameter to stringToTmpTable() but it can't return multiple rows. Unfortunatly the amount of parameters in the string is variable so i can't define as much variable as there is parameters in the string.

Finally the FIND_IN_SET() isn't the solution i need.

In the worst case I could copy / past the stringToTmpTable() process in any other procedure that needs it, but that doesn't seem like the best way to me.

Any suggestions ?

Luuk
  • 12,245
  • 5
  • 22
  • 33
G.Sannier
  • 28
  • 5

1 Answers1

0

"i'm able to translate a string into a temporary table" too, but I am using a different method:

SET @input  = 'Banana, Apple, Orange, Pears';

WITH RECURSIVE cte1 as (
   select 
      @input as s, 
      substring_index(substring_index(@input,',',1),',',-1) as w,
      length(@input)-length(replace(@input,',','')) x
   union all
   
   select
     substring_index(s,',',-x),
     trim(substring_index(substring_index(substring_index(s,',',-x),',',1),',',-1)) as w,
     x-1 x
   from cte1 where s<>'' and x>0
      )
select * from cte1

DBFIDDLE

But it's a bit of a problem to determine the real problem you have, which is causing you to ask this question. So this is not an answer, but just a different way of selecting all words from a comma-delimted string.

Luuk
  • 12,245
  • 5
  • 22
  • 33
  • I understand what you send me, but now that we have this result table : i would like to return it in a variable when i'm calling for this process : this way, if i need to edit the process, i can make my edits in a single place instead of making it in all of my calling procedure. Seems clearer ? – G.Sannier Apr 15 '22 at 12:31
  • You can use your `MyTemporaryTable` inside the stored procedure, or use the results of this common table expression I wrote in your stored procedure. I do not see why you would store this complete variable in a variable (which is not possible using MySQL) – Luuk Apr 15 '22 at 12:36
  • In order to store the this common table expression you wrote in a procedure to call it as much as i need in others procudure that might need the same treatement, but as you said, it's impossible :) thank you ! – G.Sannier Apr 15 '22 at 12:56