Add A Semicolon after every 3rd word

Debilon 431 Reputation points
2022-04-08T10:06:38.857+00:00

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.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,575 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 25,296 Reputation points
    2022-04-08T16:01:06.85+00:00

    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:

    1. 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 |  
    +----+------------------------------------------------------+------+  
    
    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Jingyang Li 5,891 Reputation points
    2022-04-08T14:27:03.757+00:00

    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

    */

    1 person found this answer helpful.