CODEWITHSUNDEEP
sql-server
Karthik
Karthik

Reputation: 149

How split a string a string based on position in SQL Server

String input: xxxxyyyyzzzz

String output: xx xx yy yy zz zz

Please split the above string in SQL Server. I am trying to use replace function something like 

PARSENAME(REPLACE(@str, ',', '.'), 2)

but it's not working

Upvotes: 1

Views: 872

Answers (3)

gofr1
gofr1

Reputation: 15987

Another way with CTE and STUFF:

DECLARE @s nvarchar(max) = 'xxxxyyyyzzzz'

;WITH cte AS (
    SELECT  @s as s,
            1 as lev
    UNION ALL
    SELECT  STUFF(s,3*lev,0,' '),
            lev+1
    FROM cte
    WHERE 3*lev < LEN(s)
)

SELECT top 1 s
FROM cte
ORDER BY lev desc

Output:

xx xx yy yy zz zz

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28920

Using Numbers table..

declare @string varchar(100)='xxxxyyyyzzzz'

;With cte
as
(select case when number%2<>0 then substring(@string,number,2) else null end as new from Numbers
where number<len(@string) 
)
select replace(stuff((select 
','+new 
 from cte where new is not null
 for xml path('')),1,1,''),',', ' ')

Output:
xx xx yy yy zz zz

Upvotes: 1

Ullas
Ullas

Reputation: 11556

Use a while loop.

Query

DECLARE @str AS varchar(100) = 'xxxxyyyyzzzz';
DECLARE @i AS int = 1;
DECLARE @res AS varchar(250) = '';

WHILE (LEN(@str) + 1 >= @i)
BEGIN
  SET @res += SUBSTRING(@str, @i, 2) + ' ';
  SET @i = @i + 2;
END
SELECT @res as [output];

Result

+-------------------+
| output            |
+-------------------+
| xx xx yy yy zz zz | 
+-------------------+

Demo

Upvotes: 1

Related Questions