title: how to create a table-valued function in mysql
body: <p>I'm migrating from SqlServer 2008 to MySql and I have a lot of stored procedure that use a generic table-value function that split a varchar.
  The sqlserver function declaration is:</p>
  
  <pre><code>CREATE FUNCTION [dbo].[fnSplit](
      @sInputList VARCHAR(8000)       -- List of delimited items
    , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
  ) RETURNS @List TABLE (item varchar(50))
  
  /* SAMPLE
  select * from fnSplit('12 345 67',' ')
  select * from fnSplit('12##34#5##67','##')
  */
  
  BEGIN
  
      DECLARE @sItem VARCHAR(8000)
  
      WHILE CHARINDEX(@sDelimiter,@sInputList,0) &lt;&gt; 0
      BEGIN
          SELECT  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
  
          IF LEN(@sItem) &gt; 0 
              INSERT INTO @List SELECT @sItem
      END 
  
      IF LEN(@sInputList) &gt; 0
          INSERT INTO @List SELECT @sInputList -- Put the last item in
  
      RETURN
  END
  </code></pre>