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
x
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;