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 udf_SplitString()
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
Test udf_SplitString()
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
Keep it simple :-)