Split a single row into multiple row

Question

How to split a single row into multiple row ?

DECLARE @var VARCHAR(50)
SELECT @Var = 'brook|456|US'

SELECT SPLIT(@var)

Result:

brook
456
US 

Answer 1

Using a tally table:

declare @parameter varchar(4000)
set @parameter = 'brook|456|US'
set @parameter = '|' +  @parameter + '|' -- add delimiter

;with 
    e1 as(select 1 as N union all select 1), -- 2 rows
    e2 as(select 1 as N from e1 as a, e1 as b), -- 4 rows
    e3 as(select 1 as N from e2 as a, e2 as b), -- 16 rows
    e4 as(select 1 as N from e3 as a, e3 as b), -- 256 rows
    tally as (select row_number() over(order by N) as N from e4
)
    select 
        substring(@parameter, N+1, charindex('|', @parameter, N+1) - N-1)
    from tally
    where 
        N < len(@parameter)
        and substring(@parameter, N, 1) ='|'

Answer 2

Try this. You need to create a Table Valed Function not a Scalar Function

CREATE FUNCTION dbo.Split (@ip_string VARCHAR(5000),@delimiter char(1))
returns TABLE
AS
    RETURN
      (SELECT Split.a.value('.', 'VARCHAR(100)') Split_col
       FROM   (SELECT Cast ('<M>' + Replace(@ip_string, @delimiter, '</M><M>')
                            + '</M>' AS XML) AS Data) AS A
              CROSS APPLY Data.nodes ('/M') AS Split(a))

go

DECLARE @var VARCHAR(50)
SELECT @Var = 'brook|456|US'

SELECT * FROM   dbo.Split(@var,'|') 

Output :

+===========+
| Split_col | 
+===========+
| brook     |
+-----------+
| 456       |  
+-----------+
| US        |
+-----------+