0

I have MSSQLserver DB which i am going to migrate to MYSQL. I have converted almost all stored procedures but i am not able to convert mssql user defined function to mysql function.

MSSQL Fuction:

create Function [dbo].[CsvSplit] ( @Array varchar(max),@separator varchar(5)) 
returns @IntTable table 
    (RValue nvarchar(400))
AS
begin

--  declare @separator char(1)
--  set @separator = ','

    declare @separator_position int 
    declare @array_value varchar(1000) 

    set @array = @array + @separator

    while patindex('%'+@separator+'%' , @array) <> 0 
    begin

      select @separator_position =  patindex('%'+@separator+'%' , @array)
      select @array_value = left(@array, @separator_position - 1)
    if len(@array_value)>0 
        Insert @IntTable
        Values (@array_value )

      select @array = stuff(@array, 1, @separator_position, '')
    end

    return
end

Usage:

SELECT TOP 1 lower(RValue) FROM CsvSplit('s,d,b',',')

Please can any one help me to convert this same function with same parameter to mysql??

SHEKHAR SHETE
  • 5,964
  • 15
  • 85
  • 143
  • You can not just convert the code to MySQL that simple. [Check out this](https://stackoverflow.com/a/20108439/9290012) – Marco Sadowski Feb 26 '18 at 07:12
  • Even TOP is not a mysql command – P.Salmon Feb 26 '18 at 08:09
  • I know that mysql has 'limit' similar to sqlserver 'top'. please read the post before commenting @P.Salmon i have already mentioned i have sqlserver version and i want to convert to mysql. – SHEKHAR SHETE Feb 26 '18 at 10:13
  • An exact conversion is not possible, as MySQL does not support to return a resultset from a function (so you cannot use `select * from function()`, but you could use a proc that inserts into a temp table and then select from it). For ways to split your string in MySQL, check [Can you split/explode a field in a MySQL query?](https://stackoverflow.com/q/471914/6248528), if you e.g. always just need a single n'th value, try [this](https://stackoverflow.com/a/43156526/6248528), if you need a table, try [this](https://stackoverflow.com/a/32586238/6248528) (you can insert it into a temptable) – Solarflare Feb 26 '18 at 10:48

0 Answers0