Check string with delimiter and return as comma seperated string using SQL Server

Create Function dbo.CCMailToWithComma  --'test@test.com,example@gmail.com,test@yahoo.com'
(
 @MailSentTo varchar(max)
)
RETURNS VARCHAR(MAX)
BEGIN
    declare @list varchar(max)
    set @MailSentTo=dbo.Remove_Duplicate_Entry(@MailSentTo,',')
 
;with tmp
  (CorrMailSentToName,Data) as (
select
LEFT(@MailSentTo, CHARINDEX(',',@MailSentTo+',')-1)
,STUFF(@MailSentTo, 1, CHARINDEX(',',@MailSentTo+','), '')
union all
select
LEFT(Data, CHARINDEX(',',Data+',')-1)
,STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp
where CorrMailSentToName > ''
)
select
@list=coalesce(@list+',','')+a.CorrMailSentToName from tmp as a where a.CorrMailSentToName not in (SELECT CPT_email from CPT_UserMgmt)

if(@list is null or @list='')
set @list=''
else
set @list=SUBSTRING(@list,0,len(@list)-1)

    return @list
 end

Note : If Mail not found in that table then it returns
E.g - test@test.com and test@yahoo.com not found in table then

O/p:
-----
test@test.com,test@yahoo.com

---------------------------------------------------------------------------------------

Create  function dbo.UserNameWithComma --'Support@ebizgc.com,tst@gc.com,Support2@ebizgc.com,Support@ebizgc.com'
(
@MailSentTo varchar(max)
)
returns varchar(max)
as
begin
declare @list varchar(max)
    set @MailSentTo=dbo.Remove_Duplicate_Entry(@MailSentTo,',')
;with tmp
  (CorrMailSentToName,Data) as (
select
LEFT(@MailSentTo, CHARINDEX(',',@MailSentTo+',')-1)
,STUFF(@MailSentTo, 1, CHARINDEX(',',@MailSentTo+','), '')
union all
select
LEFT(Data, CHARINDEX(',',Data+',')-1)
,STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp
where CorrMailSentToName > ''
)

select
@list=coalesce(@list+',','')+b.CPT_FirstName+' '+b.CPT_LastName from tmp as a
inner join CPT_UserMgmt as b on b.CPT_email=a.CorrMailSentToName
where a.CorrMailSentToName is not null and len(a.CorrMailSentToName)>0
return @list
end