Convert T-SQL stored procedure query to MySQL

Question

I am working on convert queries from MS SQL Server to MySQL

My SQL Server Query is as per below.

SELECT      userId,IndustriesID,value AS ProvIndusID
FROM        #Talent_list
CROSS APPLY STRING_SPLIT(IndustriesID,',')

I am stuck here to convert string to row and cross apply.

Need output as per the this picture

Please provide more info on what you're trying to do, sample data and expected result. Read ["How do I ask a good question"](https://stackoverflow.com/help/how-to-ask). — FanoFN, Mar 25 '21 at 06:11
That image has nothing to do with your actual SQL query, and why does your code example have the IF statement... — Chris Schaller, Mar 25 '21 at 08:10
CROSS APPLY in MySQL https://stackoverflow.com/q/36869221/1690217 — Chris Schaller, Mar 25 '21 at 08:11
mysql does not have split_string either https://stackoverflow.com/questions/34992575/mysql-substring-extraction-using-delimiter - and others are out there. — P.Salmon, Mar 25 '21 at 08:16

score 0 · Accepted Answer · answered Mar 25 '21 at 12:30

MySQL doesn't have functions that return tables. You can replace the logic with a recursive CTE:

with recursive cte as (
      select state, cast(null as char(100)) as city, cities, 0 as lev
      from talent_list
      union all
      select state, substring_index(cities, ',', 1),
             substr(cities, length(substring_index(cities, ',', 1)) + 2), lev + 1
      from cte
      where cities <> '' and lev < 5
     )    
select state, city
from cte
where lev > 0;

Here is a db<>fiddle.

Convert T-SQL stored procedure query to MySQL

1 Answers1