I have two string of the same length and would like to shuffle them together
string A: abcdefg
string B: w2j4k0R
Result: wa2bjc4dke0fRg
Any thoughts on this? Thanks in advanced
If you have a tally table or function, it's pretty easy. I have a Tally table function to use this. It is below
declare @s1 varchar(10) = 'abcdefg',
@s2 varchar(10) = 'w2j4k0R',
@String varchar(max) = ''
select @String = @String + case when Len(@s1) > N then substring(@s1, n,1) else '' end + case when Len(@s2) > N then substring(@s2, n, 1) else '' end
from dbo.Tally(1,10)
select @String
Tally Table:
CREATE FUNCTION [dbo].[Tally]
(
@pMin BIGINT
,@pMax BIGINT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
WITH T1(F) AS
(
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1
),
T2(F) AS
(SELECT 1 FROM T1 A, T1 B),
T3(F) AS
(SELECT 1 FROM T2 A, T2 B),
T4(F) AS
(SELECT 1 FROM T3 A, T3 B),
cteTally(N) AS
(
SELECT TOP ((@pMax-(((ABS(@pMin)+@pMin)/2)))+1)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
+ ((((ABS(@pMin)+@pMin)/2))-1)
FROM T4
)
SELECT
N
FROM
cteTally T
;Try
Declare @stringA as varchar(10) = 'abcdefg'
Declare @stringB as varchar(10) = 'w2j4k0R'
select (select '' + a.x
from
(
select Rtrim(SUBSTRING(@stringB, a.rn , 1) + SUBSTRING(@stringA, a.rn , 1)) [x]
from
(
select top
(case when LEN(@stringA) > LEN(@stringB) then LEN(@stringA) else LEN(@stringB) end ) ROW_NUMBER() over(order by a.object_id asc) [rn]
from sys.objects a
) a
) a
FOR XML PATH('')) as [Result]
1 Like
Hi havey2,
if you would like to use recursive cte, you can use my solution:
--variable definition
DECLARE
--[PARAM] BEGIN
@s1 varchar(10) = 'abcdefg',
@s2 varchar(10) = 'w2j4k0R',
--[PARAM] END
@len_of_strings int,
@string varchar(max) = '';
--initialisation
SET @len_of_strings = len(@s1);
--collecting data
WITH cte
AS
(
SELECT
1 AS n,
substring(@s2, 1, 1) as s1,
substring(@s1, 1, 1) as s2 -- anchor member
UNION ALL
SELECT n + 1,
substring(@s2, n + 1, 1),
substring(@s1, n + 1, 1) -- recursive member
FROM cte
WHERE n < @len_of_strings -- terminator
)
SELECT @string = @string + s1 + s2
FROM cte;
--final value select
select @string;
Regards,
gigawatt38
MCSA: SQL 2016 Database Development
MCSE: Data Management and Analytics
Tested by: http://rextester.com/l/sql_server_online_compiler
For reasonable length strings, say ~25 bytes or less, just use a simple hard-coded method. Less overhead and complexity.
;WITH cte_test_data AS (
SELECT stringA = CAST('abcdefg' AS varchar(20)),
stringB = CAST('w2j4k0R' AS varchar(20))
)
SELECT
CASE WHEN LEN(stringA) >= 1 THEN SUBSTRING(stringA, 1, 1) ELSE '' END +
CASE WHEN LEN(stringB) >= 1 THEN SUBSTRING(stringB, 1, 1) ELSE '' END +
CASE WHEN LEN(stringA) >= 2 THEN SUBSTRING(stringA, 2, 1) ELSE '' END +
CASE WHEN LEN(stringB) >= 2 THEN SUBSTRING(stringB, 2, 1) ELSE '' END +
CASE WHEN LEN(stringA) >= 3 THEN SUBSTRING(stringA, 3, 1) ELSE '' END +
CASE WHEN LEN(stringB) >= 3 THEN SUBSTRING(stringB, 3, 1) ELSE '' END +
CASE WHEN LEN(stringA) >= 4 THEN SUBSTRING(stringA, 4, 1) ELSE '' END +
CASE WHEN LEN(stringB) >= 4 THEN SUBSTRING(stringB, 4, 1) ELSE '' END +
CASE WHEN LEN(stringA) >= 5 THEN SUBSTRING(stringA, 5, 1) ELSE '' END +
CASE WHEN LEN(stringB) >= 5 THEN SUBSTRING(stringB, 5, 1) ELSE '' END +
CASE WHEN LEN(stringA) >= 6 THEN SUBSTRING(stringA, 6, 1) ELSE '' END +
CASE WHEN LEN(stringB) >= 6 THEN SUBSTRING(stringB, 6, 1) ELSE '' END +
CASE WHEN LEN(stringA) >= 7 THEN SUBSTRING(stringA, 7, 1) ELSE '' END +
CASE WHEN LEN(stringB) >= 7 THEN SUBSTRING(stringB, 7, 1) ELSE '' END +
CASE WHEN LEN(stringA) >= 8 THEN SUBSTRING(stringA, 8, 1) ELSE '' END +
CASE WHEN LEN(stringB) >= 8 THEN SUBSTRING(stringB, 8, 1) ELSE '' END +
CASE WHEN LEN(stringA) >= 9 THEN SUBSTRING(stringA, 9, 1) ELSE '' END +
CASE WHEN LEN(stringB) >= 9 THEN SUBSTRING(stringB, 9, 1) ELSE '' END +
CASE WHEN LEN(stringA) >= 10 THEN SUBSTRING(stringA, 10, 1) ELSE '' END +
CASE WHEN LEN(stringB) >= 10 THEN SUBSTRING(stringB, 10, 1) ELSE '' END +
CASE WHEN LEN(stringA) >= 11 THEN SUBSTRING(stringA, 11, 1) ELSE '' END +
CASE WHEN LEN(stringB) >= 11 THEN SUBSTRING(stringB, 11, 1) ELSE '' END +
CASE WHEN LEN(stringA) >= 12 THEN SUBSTRING(stringA, 12, 1) ELSE '' END +
CASE WHEN LEN(stringB) >= 12 THEN SUBSTRING(stringB, 12, 1) ELSE '' END +
CASE WHEN LEN(stringA) >= 13 THEN SUBSTRING(stringA, 13, 1) ELSE '' END +
CASE WHEN LEN(stringB) >= 13 THEN SUBSTRING(stringB, 13, 1) ELSE '' END +
CASE WHEN LEN(stringA) >= 14 THEN SUBSTRING(stringA, 14, 1) ELSE '' END +
CASE WHEN LEN(stringB) >= 14 THEN SUBSTRING(stringB, 14, 1) ELSE '' END +
CASE WHEN LEN(stringA) >= 15 THEN SUBSTRING(stringA, 15, 1) ELSE '' END +
CASE WHEN LEN(stringB) >= 15 THEN SUBSTRING(stringB, 15, 1) ELSE '' END +
CASE WHEN LEN(stringA) >= 16 THEN SUBSTRING(stringA, 16, 1) ELSE '' END +
CASE WHEN LEN(stringB) >= 16 THEN SUBSTRING(stringB, 16, 1) ELSE '' END +
CASE WHEN LEN(stringA) >= 17 THEN SUBSTRING(stringA, 17, 1) ELSE '' END +
CASE WHEN LEN(stringB) >= 17 THEN SUBSTRING(stringB, 17, 1) ELSE '' END +
CASE WHEN LEN(stringA) >= 18 THEN SUBSTRING(stringA, 18, 1) ELSE '' END +
CASE WHEN LEN(stringB) >= 18 THEN SUBSTRING(stringB, 18, 1) ELSE '' END +
CASE WHEN LEN(stringA) >= 19 THEN SUBSTRING(stringA, 19, 1) ELSE '' END +
CASE WHEN LEN(stringB) >= 19 THEN SUBSTRING(stringB, 19, 1) ELSE '' END +
CASE WHEN LEN(stringA) >= 20 THEN SUBSTRING(stringA, 20, 1) ELSE '' END +
CASE WHEN LEN(stringB) >= 20 THEN SUBSTRING(stringB, 20, 1) ELSE '' END
FROM cte_test_datadeclare @string1 varchar(10) = 'abcdefg', @string2 varchar(10) = 'w2j4k0R'
declare @LengthOfString int = len(@string1)
declare @n int = 1
declare @string3 as varchar(50) = ''
while (@n <=len(@string1))
begin
set @string3 = @string3+substring(@string1,@n,1)+substring(@string2,@n,1)
set @n = @n + 1
end
print @string3
declare @string1 varchar(10)
declare @string2 varchar(10)
set @string1 = 'abcdefg'
set @string2 = 'w2j4k0R'
select @string1,@string2
select
substring(@string2,1,1)
+substring(@string1,1,1)
+substring(@string2,2,1)
+substring(@string1,2,1)
+substring(@string2,3,1)
+substring(@string1,3,1)
+substring(@string2,4,1)
+substring(@string1,4,1)
+substring(@string2,5,1)
+substring(@string1,5,1)
+substring(@string2,6,1)
+substring(@string1,6,1)
+substring(@string2,7,1)
+substring(@string1,7,1)
as Result