Add A Semicolon after every 3rd word

Question

I need to add a semicolon after every 3rd word in a string

1. Sometimes there might already be a semicolon after the 3rd word in the string in this case i do not need to add another one
2. if the string is only 3 words, i do not need to add a semicolon
3. if after the 3rd word the next word is AKA then i want to move the aka and what follows to a new column called aka. DECLARE @Bluemchen AS TABLE (id int identity(1,1) PRIMARY KEY, NAME varchar(100));
   INSERT INTO @Bluemchen VALUES
   ('TEXAS BEATRICE J; TEXAS DONNA RAE'),
   ('BRODWAY STEVEN M BRODWAY MOLLY M BRODWAY ROBERT W'),
   ('RIBEYE JOHN K; RIBEYE JERRY C; RIBEYE CONSTANCE'),
   ('ROMERO MARIO R; ROMERO SILVIA E'),
   ('GAITAN KATHLEEN PENNY FELTON NICOLE GAITAN'),
   ('SHMUCKLER ARTHUR JOHN AKA A J'); WITH CTE AS (
   SELECT id, name, LEN(Name)-LEN(REPLACE(Name, ' ', '')) as Location,
   LEN(Name)-LEN(REPLACE(Name, ' ', '')) as po
   FROM @Bluemchen )
   SELECT name,
   SpaceLocation =
   CASE
   WHEN po = 3 THEN REPLACE(name,' ',' ')
   --SUBSTRING(Name,0,Location)
   ELSE Name
   END,
   Location
   FROM CTE

I'm able to find the 3rd position with 'WHEN po = 3 THEN REPLACE(name,' ',' ')'
but then i am kind of getting stuck.

Please T-SQL only. no XML.

Answer 1

Hi @Debilon ,

Regardless of the resistance here is XML/XQuery based solution.
It satisfies all 3 required business rules.

You probably noticed that the JSON based solution is not satisfying rule #2:

2. if the string is only 3 words, i do not need to add a semicolon

SQL

-- DDL and sample data population, start  
DECLARE @tbl AS TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, tokens VARCHAR(100));  
INSERT INTO @tbl VALUES  
('TEXAS BEATRICE J; TEXAS DONNA RAE'),  
('BRODWAY STEVEN M BRODWAY MOLLY M BRODWAY ROBERT W'),  
('RIBEYE JOHN K; RIBEYE JERRY C; RIBEYE CONSTANCE'),  
('ROMERO MARIO R; ROMERO SILVIA E'),  
('GAITAN KATHLEEN PENNY FELTON NICOLE GAITAN'),  
('SHMUCKLER ARTHUR JOHN AKA A J'),  
('One Two Three');  
-- DDL and sample data population, end  
  
DECLARE @separator CHAR(1) = SPACE(1);  
  
SELECT t.*, cnt --, t0.*  
	, REPLACE(c.query('  
	for $x in /root/r  
	let $pos := count(/root/r[. << $x[1]]) + 1  
	return if ($pos mod 3 = 0 and sql:column("t2.cnt")!=3) then concat(data($x), ";")  
		else data($x)  
	').value('.', 'VARCHAR(100)'),';;',';') AS Result  
	, aka  
FROM @tbl AS t  
-- find AKA position  
CROSS APPLY (SELECT CHARINDEX(' aka ',tokens)) AS x(aka_pos)	  
-- separate before and after AKA  
CROSS APPLY (SELECT  
	IIF(aka_pos > 0, LEFT(tokens, aka_pos - 1), tokens),  
	IIF(aka_pos > 0, RIGHT(tokens, LEN(tokens) - aka_pos - 4), null)  
	) AS t0(tokens, aka)  
CROSS APPLY (SELECT TRY_CAST('<root><r>' +   
      REPLACE(t0.tokens, @separator, '</r><r>') +   
      '</r></root>' AS XML)) AS t1(c)  
CROSS APPLY (SELECT c.value('count(/root/r)', 'INT')) AS t2(cnt);  

Output

+----+------------------------------------------------------+------+  
| ID |                        Result                        | aka  |  
+----+------------------------------------------------------+------+  
|  1 | TEXAS BEATRICE J; TEXAS DONNA RAE;                   | NULL |  
|  2 | BRODWAY STEVEN M; BRODWAY MOLLY M; BRODWAY ROBERT W; | NULL |  
|  3 | RIBEYE JOHN K; RIBEYE JERRY C; RIBEYE CONSTANCE      | NULL |  
|  4 | ROMERO MARIO R; ROMERO SILVIA E;                     | NULL |  
|  5 | GAITAN KATHLEEN PENNY; FELTON NICOLE GAITAN;         | NULL |  
|  6 | SHMUCKLER ARTHUR JOHN                                | A J  |  
|  7 | One Two Three                                        | NULL |  
+----+------------------------------------------------------+------+  

Answer 2

DECLARE @Bluemchen AS TABLE (id int identity(1,1) PRIMARY KEY, NAME varchar(100));
INSERT INTO @Bluemchen VALUES
('TEXAS BEATRICE J; TEXAS DONNA RAE'),
('BRODWAY STEVEN M BRODWAY MOLLY M BRODWAY ROBERT W'),
('RIBEYE JOHN K; RIBEYE JERRY C; RIBEYE CONSTANCE'),
('ROMERO MARIO R; ROMERO SILVIA E'),
('GAITAN KATHLEEN PENNY FELTON NICOLE GAITAN'),
('SHMUCKLER ARTHUR JOHN AKA A J');

;with mycte as (select id,value, [key], ([key]+1)%3 k
from @Bluemchen
cross apply openjson('["'+(replace(replace(NAME,';',''),' ','","')+'"]'))
)
,mycte2 as (
select id,string_agg(Case when k=0 then value+';' else value end ,' ') WITHIN GROUP ( ORDER BY id, [key]) Name

from mycte
group by id)

select id,
max(case when charindex('; aka',Name)> 0 then stuff(Name,charindex('; aka',Name),len(Name),'')+';' else Name end) Name,
max(case when charindex('; aka',Name)> 0 then Replace(stuff(Name,1,charindex('; aka',Name)+4,''),';','') else null end ) AKA
from mycte2
group by id, Name

/*
id Name AKA
1 TEXAS BEATRICE J; TEXAS DONNA RAE; NULL
2 BRODWAY STEVEN M; BRODWAY MOLLY M; BRODWAY ROBERT W; NULL
3 RIBEYE JOHN K; RIBEYE JERRY C; RIBEYE CONSTANCE NULL
4 ROMERO MARIO R; ROMERO SILVIA E; NULL
5 GAITAN KATHLEEN PENNY; FELTON NICOLE GAITAN; NULL
6 SHMUCKLER ARTHUR JOHN; A J

*/