Use function stuff for xml path in MySQL

Question

I have a SQL Server query and I want to use MySQL but I don't know about using the MySQL function

MyQuery

declare @query  as varchar(max)
declare @data  as varchar(max)

set @query =  STUFF((select ','+column_name from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME = 'tbl_TestStockClaris' and ORDINAL_POSITION >= 4
for xml path('')),1,1,'')

set @data = 'select top 10 kodescala,'+@query+' from tbl_TestStockClaris'

execute(@data);

Did you tried group_concat() in MySQL? this is the equivalent of STUFF in MSSQL. — mkRabbani, Aug 01 '19 at 04:45
mysql is very different to sqlserver in many ways, Apart from not having stuff you cannot DECLARE user defined varaibles (@varables) or EXECUTE sql statements or run code like this outside a stored program in mysq or use + as a concat shortcut. And that's just the differences in your few lines of published code. — P.Salmon, Aug 01 '19 at 05:58
@P.Salmon for the stuff function, it has successfully used group_concat, is there an idea for storing excute variables like procedure? — asep munandar, Aug 01 '19 at 06:11
Read https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-prepared-statements.html — P.Salmon, Aug 01 '19 at 06:14
@P.Salmon; how to execute the data variable value and the data value is saved to the query.. my query. set data = ('select group_concat(column_name) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = "tbl_TestStockClaris" and ORDINAL_POSITION >= 4'); PREPARE query FROM concat('select data from tbl_TestStockClaris'); EXECUTE query ; — asep munandar, Aug 01 '19 at 07:56
Please edit your question to include the entire stored program you have created including the delimiters , if you are not using a stored program then you have to use a user defined variable to build your sql statement see https://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference/1010042. Can you provide the table definition as text in the question? — P.Salmon, Aug 01 '19 at 08:15

Ed Bangga · Answer 1 · 2019-08-01T03:39:53.757

0

treat your sql query variable as string.

declare @query  as varchar(max)
declare @data  as varchar(max)

set @query =  'STUFF((select '',''+column_name from INFORMATION_SCHEMA.COLUMN 
where TABLE_NAME = ''kodescala'' and ORDINAL_POSITION >= 4
for xml path('''')),1,1,'''')';

set @data ='select top 10 kodescala,'+@query+' from tbl_TestStockClaris';

execute(@data);

edited Aug 01 '19 at 03:39

answered Aug 01 '19 at 02:10

Ed Bangga

12,879
4
16
30

My query is running on sql server but I just want to use mysql but I don't know the mysql function – asep munandar Aug 01 '19 at 03:33

score 0 · Answer 2 · answered Aug 01 '19 at 08:29

an example of a prepared statement

set @data = (select 
                 concat('select ' , gc, ' from dates limit 10;')
                from
                (select group_concat(column_name) gc from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'dates') s

                ); 

select @data;
PREPARE query from @data; 
EXECUTE query ;
deallocate prepare query;

Use function stuff for xml path in MySQL

2 Answers2