So far i have seen on the web for solution to reorder or rearrange a comma separated varchar/string value. but did not find a solution where u could do it in a simple way without using temporary tables, cursors or table variables in MS Sql Server or MySql.
this a sample for Mysql
set @newVal='7749,7750,7751,7741,7742' ;
set @P_vcQueryMain:=concat('select group_concat(Expid order by expId) from (Select ''',REPLACE(@newVal,',',''' as ExpId union Select '''),''' as ExpId order by ExpId) ExpVals into @newVal ;');
PREPARE stmt FROM @P_vcQueryMain;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt;
select @newVal;
Input value : [7749,7750,7751,7741,7742]
Output value : [7741,7742,7749,7750,7751]set @newVal='banana,apple,cake,cream,apple pie' ;
set @P_vcQueryMain:=concat('select group_concat(Expid order by expId) from (Select ''',REPLACE(@newVal,',',''' as ExpId union Select '''),''' as ExpId order by ExpId) ExpVals into @newVal ;');
PREPARE stmt FROM @P_vcQueryMain;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt;
select @newVal;
Input value : [banana,apple,cake,cream,apple pie]Output value : [apple,apple pie,banana,cake,cream]here @newVal contains the values to be reordered and the same variable returns the correctly reordered string back
this a sample for MsSql Server
declare @newVal varchar(max);
declare @P_vcQueryMain varchar(max);
set @newVal='7749,7751,7741,7750,7742' ;
set @P_vcQueryMain='declare @RetVal varchar(max);; select @RetVal =coalesce(@RetVal + '','' ,'''') + Expid from (Select ''' + REPLACE(@newVal,',',''' as ExpId union Select ''')+ ''' as ExpId) ExpVals order by ExpId; select @RetVal;';
exec(@P_vcQueryMain);
Input value : [7749,7750,7751,7741,7742]
Output value : [7741,7742,7749,7750,7751]
declare @newVal varchar(max);
declare @P_vcQueryMain varchar(max);
set @newVal='banana,apple,cake,cream,apple pie' ;
set @P_vcQueryMain='declare @RetVal varchar(max);; select @RetVal =coalesce(@RetVal + '','' ,'''') + Expid from (Select ''' + REPLACE(@newVal,',',''' as ExpId union Select ''')+ ''' as ExpId) ExpVals order by ExpId; select @RetVal;';
exec(@P_vcQueryMain);
Input value : [banana,apple,cake,cream,apple pie]Output value : [apple,apple pie,banana,cake,cream]
this solution reduces the query cost, as the string is reordered in just one sql statement rather than using tables or cursors which are memory consuming.
any better suggestion welcome, pls write a comment if u find the post helpfull.
.blog
