Efficient way to string split using CTE

You seem dead set on using a CTE, so try this:

DECLARE @YourTable table (RowID int, Layout varchar(200))
INSERT @YourTable VALUES (1,'hello,world,welcome,to,tsql')
INSERT @YourTable VALUES (2,'welcome,to,stackoverflow')

;WITH SplitSting AS
(
    SELECT
        RowID,LEFT(Layout,CHARINDEX(',',Layout)-1) AS Part
            ,RIGHT(Layout,LEN(Layout)-CHARINDEX(',',Layout)) AS Remainder
        FROM @YourTable
        WHERE Layout IS NOT NULL AND CHARINDEX(',',Layout)>0
    UNION ALL
    SELECT
        RowID,LEFT(Remainder,CHARINDEX(',',Remainder)-1)
            ,RIGHT(Remainder,LEN(Remainder)-CHARINDEX(',',Remainder))
        FROM SplitSting
        WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)>0
    UNION ALL
    SELECT
        RowID,Remainder,null
        FROM SplitSting
        WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)=0
)
SELECT * FROM SplitSting ORDER BY RowID

OUTPUT:

RowID       Part                   
----------- -----------------------
1           hello                  
1           world                  
1           welcome                
1           to                     
1           tsql                   
2           welcome                
2           to                     
2           stackoverflow          

(8 row(s) affected)

here is an excellent article on splitting strings in SQL Server: "Arrays and Lists in SQL Server 2005 and Beyond, When Table Value Parameters Do Not Cut it" by Erland Sommarskog

EDIT here's another version (but you need a numbers table) returns same results as above:

;WITH SplitValues AS
(
    SELECT
        RowID,ListValue
        FROM (SELECT
                  RowID, LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(',', List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT RowID, ',' + Layout + ',' AS List2
                           FROM @YourTable
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = ','
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''
)
SELECT * FROM SplitValues

see here for a numbers table: What is the best way to create and populate a numbers table?

it's my best solution using CTE:

DECLARE @Char VARCHAR(MAX) = '10||3112||||aaaa||'
DECLARE @Separador CHAR(2) = '||'

;WITH Entrada AS(
    SELECT
        CAST(1 AS Int) As Inicio,
        CHARINDEX(@Separador, @Char) As Fim
    UNION ALL
    SELECT
        CAST(Fim + LEN(@Separador) AS Int) As Inicio,
        CHARINDEX(@Separador, @Char, Fim + 1) As Fim
    FROM Entrada
    WHERE CHARINDEX(@Separador, @Char, Fim + 1) > 0
)
SELECT 
    SUBSTRING(@Char, Inicio, Fim - Inicio)
FROM Entrada
WHERE (Fim - Inicio) > 0

From NullRef's Answer

Function without set operation will be faster according to my understanding of sql server

so this will be very efficient

CREATE FUNCTION fnSplitString(@str nvarchar(max),@sep nvarchar(max))
RETURNS TABLE
AS
RETURN
    WITH a AS(
        SELECT CAST(0 AS BIGINT) as idx1,CHARINDEX(@sep,@str) idx2
        UNION ALL
        SELECT idx2+1,CHARINDEX(@sep,@str,idx2+1)
        FROM a
        WHERE idx2>0
    )
    SELECT SUBSTRING(@str,idx1,COALESCE(NULLIF(idx2,0),LEN(@str)+1)-idx1) as value
    FROM a