I’m currently working on a project where the data I’m receiving is being given to me in a messed up order. I need every 2 characters to switch places in a string. Currently I have the below code, but it is ungodly slow in a function or stored procedure. Any help would be appreciated.
Ex: FK741 OCEV
needs to be KF47 1COVE
ALTER FUNCTION [dbo].[UnscrambleData] ( @scrambled varchar(50) ) RETURNS varchar(50) AS BEGIN Declare @unscrambled varchar(50) Declare @temp1 varchar(1) DECLARE @cnt INT = 0; Declare @cnt_Total INT =len(@scrambled) WHILE @cnt < = @cnt_total if((@cnt%2)=0) begin set @unscrambled=CONCAT( @unscrambled,SUBSTRING(@scrambled, @cnt, 1),@temp1) set @temp1='' end else if (@cnt_Total%2<>0 and @cnt_Total-@cnt<2) begin set @unscrambled=CONCAT( @unscrambled,SUBSTRING(@scrambled, @cnt, 1)) end else begin set @temp1= SUBSTRING(@scrambled, @cnt, 1) end SET @cnt = @cnt + 1; RETURN @unscrambled; END
Advertisement
Answer
You can do that as
SELECT STRING_AGG(REVERSE(V), '') FROM ( VALUES ('FK741 OCEV') ) T(Str) CROSS APPLY ( SELECT SUBSTRING(Str, Number-1, 2) FROM Master..spt_values --Tally table WHERE [Type] = 'P' AND Number BETWEEN 1 AND LEN(Str) AND Number % 2 = 0 ) TT(V)
Returns:
KF47 1COVE
If you have more than one string then
SELECT STRING_AGG(REVERSE(V), '') FROM ( VALUES (1, 'FK741 OCEV'), (2, 'ABC DEF GH'), (3, 'THIRD STRING') ) T(Id, Str) CROSS APPLY ( SELECT SUBSTRING(Str, Number-1, 2) FROM Master..spt_values WHERE [Type] = 'P' AND Number BETWEEN 1 AND LEN(Str) AND Number % 2 = 0 ) TT(V) GROUP BY T.Id;