I found this solution interesting, but I was especially intrigued by the inconsistent results. So I did my own testing by breaking down the solution.
I suspect the reason some people are getting only a single A as result is not due to the version of SQL, but the version of Management Studio.
The solution is based on the introduction of the Char(0) character as a delimiter, and I think this specific character is playing havoc with Management studio.
Hers is my findings and I would be curious to know if the posters who had issues could verify if this would work for them.
DECLARE@A VARCHAR(6) = 'ABCDEF'
Select CAST(CAST(CAST(@A AS NVARCHAR) AS VARBINARY) AS VARCHAR), Len(CAST(CAST(CAST(@AAS NVARCHAR) AS VARBINARY) AS VARCHAR))
-- You have a 12 character string but only 1 is displayed
-- For some reason the ASCII NUL(Char(0)) introduced from the data type changes, iscausing the whole string to not be displayed.
-- I suspect this is a Management Studio limitation not SQL's handling of the data (What You See Is NOT What you Got)
-- Replace the ASCII NUL character and the result displays as expected
Select Replace(CAST(CAST(CAST(@A AS NVARCHAR) AS VARBINARY) AS VARCHAR),Char(0),'|')
-- Modified SQL that should work regardless of Management Studio version. With theexception of the dangling delimiter that will create an extra empty row in theresulting table.
SELECT value FROM STRING_SPLIT(Replace(CAST(CAST(CAST(@A AS NVARCHAR) AS VARBINARY) ASVARCHAR),Char(0),'|'), '|')