How can I return the first 3 characters after every comma in a string in TSQL-CodePudding

I have a string that I need to seperate into values seperated by commas. I have achieved this part with the below REPLACE statement:

declare @mc varchar(200) declare @mc1 varchar(200) select @mc = 'FRED&#g4;&#4g;MARY&#g4;&#4g;BILL&#g4;&#4g;TIMOTHY&#g4;&#4g;JOHNATHAN'

select @mc1 = REPLACE(@mc, '&#g4;&#4g;',', ')

The replace returns a string 'FRED, MARY, BILL, TIMOTHY, JOHNATHAN'

I then want to have another variable that will return the first 3 characters of each value before the commas, so the above string would be returned as:

'FRE, MAR, TIM, JOH'

Anyone know how I can achieve this?

Also happy for this to be done directly to the original @mc variable

CodePudding user response：

ON SQL Server 2017 you can make use of openJson to split the string into manageble segments and then string_agg to assemble the desired result:

declare @mc varchar(100)='FRED&#g4;&#4g;MARY&#g4;&#4g;BILL&#g4;&#4g;TIMOTHY&#g4;&#4g;JOHNATHAN'

select String_Agg(v, ', ')
from (select @mc)x(s)
cross apply (
    select Left(j.[value],3) v,  Convert(tinyint,j.[key]) Seq 
    from OpenJson(Concat('["',replace(s,';', '","'),'"]')) j
    where Convert(tinyint,j.[key]) % 2 = 0
)j;

Demo