An user-defined function to convert comma separated values (CSV) to an Array (table) is always needed.
I wrote one that uses a multi-character delimiter and returns not/trimmed values. The string searches for the delimiter, inserts the left slice into the resulting table and cuts the left slice from the string. The ID column is used to pick the second, third etc. value from the string or to order the array.

Create database

USE [master];
GO

IF (DB_ID('PL') IS NOT NULL)
	BEGIN
		ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE PL;
	END
GO

CREATE DATABASE PL;
GO

USE PL;
GO

CREATE FUNCTION [dbo].[udf_SplitString]
	(
		@InputString NVARCHAR(MAX)
		, @DelimiterString NVARCHAR(128)
		, @IsResultTrimmed BIT = 1
	) 
RETURNS @ResultTable TABLE
	(
		ID INT IDENTITY(1, 1) PRIMARY KEY
		, [Value] NVARCHAR(MAX)
	)
AS
BEGIN

DECLARE
	@DelimiterLength INT
	, @StartPosition INT
	, @Index INT;

SELECT
	@Index = CHARINDEX(@DelimiterString, @InputString)
	, @DelimiterLength = LEN(@DelimiterString);

SELECT @InputString += @DelimiterString

WHILE (@Index > 0)
	BEGIN
		INSERT @ResultTable ([Value])
		SELECT
			CASE
				WHEN @IsResultTrimmed = 1 THEN LTRIM(RTRIM(LEFT(@InputString, (@Index - 1))))
				ELSE LEFT(@InputString, (@Index - 1))
			END;
		
		SELECT
			@InputString = RIGHT(@InputString, (LEN(@InputString) - @Index - (@DelimiterLength - 1)))
			, @Index = CHARINDEX(@DelimiterString, @InputString);
	END
	
	RETURN;
END
GO

DECLARE
	@MyTestInputString NVARCHAR(MAX)
	, @MyTestDelimiterString NVARCHAR(128);

SELECT
	@MyTestInputString = '   AAA!@#$%BBBB!@#$%CCCC     '
	, @MyTestDelimiterString = '!@#$%';

SELECT
	'>' + @MyTestInputString + '<' AS [MyTestInputString]
	, @MyTestDelimiterString AS [MyTestDelimiterString];

-- Trimmed
SELECT ID, '>' + [Value] + '<' AS [ValueTrimmed]
FROM PL.dbo.udf_SplitString(@MyTestInputString, @MyTestDelimiterString, DEFAULT);

-- Not trimmed
SELECT ID, '>' + [Value] + '<' AS [ValueNotTrimmed]
FROM PL.dbo.udf_SplitString(@MyTestInputString, @MyTestDelimiterString, 0);
GO

Delete database

USE [master];
GO

IF (DB_ID('PL') IS NOT NULL)
	BEGIN
		ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE PL;
	END
GO