SQL Server split string values into rows and columns and insert into table, Sql Server

Language: Layout:

create function dbo.DelimitedSplitN4K (
        @pString nvarchar(4000)
      , @pDelimiter nchar(1)
      )
    returns table with schemabinding as
    return
      with e1(n) as (
        select 1 union all select 1 union all select 1 union all 
        select 1 union all select 1 union all select 1 union all 
        select 1 union all select 1 union all select 1 union all select 1
      )
      , e2(n) as (select 1 from e1 a, e1 b)
      , e4(n) as (select 1 from e2 a, e2 b)
      , cteTally(n) as (select top (isnull(datalength(@pString)/2,0))
          row_number() over (order by (select null)) from e4)
      , cteStart(n1) as (select 1 union all 
          select t.n+1 from cteTally t where substring(@pString,t.n,1) = @pDelimiter)
      , cteLen(n1,l1) as(select s.n1
      ,   isnull(nullif(charindex(@pDelimiter,@pString,s.n1),0)-s.n1,4000)
        from cteStart s
      )
     select ItemNumber = row_number() over(order by l.n1)
          , Item       = substring(@pString, l.n1, l.l1)
       from cteLen l;
    go
/* ----------- */
declare @status nvarchar(max) = '123456|House,123567|Flat,789463|Car,635491|Lorry,'

select 
    nvalue = left(s.Item,charindex('|',s.Item)-1)
  , [Type] = stuff(s.Item,1,charindex('|',s.Item),'')
from dbo.DelimitedSplitN4K(@status,',') s
where s.Item <>''

View schema
Execution time: 0,03 sec, rows selected: 5, rows affected: 0, absolute service time: 0,16 sec

SQL Server split string values into rows and columns and insert into t...