SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[tb_Employee](
	[ID] [INT] NOT NULL,
	[Details] [varchar](255) NOT NULL
) ON [PRIMARY]

INSERT [dbo].[tb_Employee] ([ID], [Details]) VALUES 
(1, N'Good,Good, Good'),
(2, N'Very Good, Very Good, Bad'),
(3, N'Bad, Bad, Good'),
(4, N'Good,Good, Good, Bad'),
(5, N'Good')
5 rows affected

CREATE FUNCTION [dbo].[fn_SplitString] (
    @SourceString sql_variant,
    @Delimiter nvarchar(10) = N','
)
RETURNS @Values TABLE( Position smallint IDENTITY, cValue varchar( 8000 ) , ncValue nvarchar( 4000 ) )
AS
BEGIN

    DECLARE @NormalString    varchar( 8000 ) , @NationalString    nvarchar( 4000 ) ,
            @NormalDelimiter varchar( 10 )   , @NationalDelimiter nvarchar( 10 )   ,
            @IsNationalChar  bit             , @Position          int              ,
            @NormalValue     varchar( 8000 ) , @NationalValue     nvarchar( 4000 )
    SET @Delimiter      = COALESCE( @Delimiter, N',' )
    SET @IsNationalChar = CASE
                            WHEN SQL_VARIANT_PROPERTY( @SourceString , 'BaseType' ) IN ( 'char' , 'varchar' )
                            THEN 0
                            WHEN SQL_VARIANT_PROPERTY( @SourceString , 'BaseType' ) IN ( 'nchar' , 'nvarchar' )
                            THEN 1
                          END
    IF @IsNationalChar IS NULL RETURN
    IF @IsNationalChar = 0
    BEGIN
            SET @NormalDelimiter = @Delimiter
            SET @NormalString    = CAST( @SourceString AS varchar(8000) )

-- Do not remove any leading delimiters. If there are any leading delimiters, report them as a blank value.
--            IF LEFT( @NormalString , LEN( @NormalDelimiter ) ) = @NormalDelimiter
--                    SET @NormalString = SUBSTRING( @NormalString, LEN( @NormalDelimiter ) + 1, 8000 )
-- Always Add a Trailing Delimiter. This ensures that any trailing Delimiters in the input string
-- will result in a blank value.
--            IF RIGHT( @NormalString , LEN( @NormalDelimiter ) ) <> @NormalDelimiter
                    SET @NormalString = @NormalString + @NormalDelimiter
            WHILE( 1 = 1 )
            BEGIN
                    SET @Position     = CHARINDEX( @NormalDelimiter , @NormalString ) - 1
                    IF @Position < 0 BREAK 			-- remove the break on 0 because this causes data loss when there is consecutive delimiters and data after that as well.
                    SET @NormalValue  = LEFT( @NormalString , @Position )
                    SET @NormalString = STUFF( @NormalString , 1 , @Position + LEN( @NormalDelimiter ), '' )
                    INSERT INTO @Values ( cValue ) VALUES( @NormalValue )
            END
    END
    ELSE IF @IsNationalChar = 1
    BEGIN
            SET @NationalDelimiter = @Delimiter
            SET @NationalString    = CAST( @SourceString AS varchar(8000) )

-- Do not remove any leading delimiters. If there are any leading delimiters, report them as a blank value.
--            IF LEFT( @NationalString , LEN( @NationalDelimiter ) ) = @NationalDelimiter
--                    SET @NationalString = SUBSTRING( @NationalString, LEN( @NationalDelimiter ) + 1, 4000 )
-- Always Add a Trailing Delimiter. Any trailing Delimiters in the input string will be reported as a blank value.
--            IF RIGHT( @NationalString , LEN( @NationalDelimiter ) ) <> @NationalDelimiter
                    SET @NationalString = @NationalString + @NationalDelimiter
            WHILE( 1 = 1 )
            BEGIN
                    SET @Position     = CHARINDEX( @NationalDelimiter , @NationalString ) - 1
                    IF @Position < 0 BREAK 			-- remove the break on 0 because this causes data loss when there is consecutive delimiters and data after that as well.
                    SET @NationalValue  = LEFT( @NationalString , @Position )
                    SET @NationalString = STUFF( @NationalString , 1 , @Position + LEN( @NationalDelimiter ), '' )
                    INSERT INTO @Values ( ncValue ) VALUES( @NationalValue )
            END
    END
    RETURN
END
SELECT
    [ID],[Details],
    [cleansedDetails] = (SELECT
    STUFF((
            SELECT
            DISTINCT ','+LTRIM(RTRIM(ISNULL(ncValue,cvalue)))
            FROM
                fn_SplitString([Details], ',')
            FOR XML PATH('')) 
        ,1 ,1 ,''))
FROM [dbo].[tb_Employee]


ID
Details
cleansedDetails


1
Good,Good, Good
Good

2
Very Good, Very Good, Bad
Bad,Very Good

3
Bad, Bad, Good
Bad,Good

4
Good,Good, Good, Bad
Bad,Good

5
Good
Good
ID	Details	cleansedDetails
1	Good,Good, Good	Good
2	Very Good, Very Good, Bad	Bad,Very Good
3	Bad, Bad, Good	Bad,Good
4	Good,Good, Good, Bad	Bad,Good
5	Good	Good