SQL Server Split

DECLARE @DataSource TABLE
(
    [ID] TINYINT IDENTITY(1,1)
   ,[Value] NVARCHAR(128)
)   

DECLARE @Value NVARCHAR(MAX) = 'BAT | CAT | RAT | MAT'

DECLARE @XML xml = N'<r><![CDATA[' + REPLACE(@Value, '|', ']]></r><r><![CDATA[') + ']]></r>'

INSERT INTO @DataSource ([Value])
SELECT RTRIM(LTRIM(T.c.value('.', 'NVARCHAR(128)')))
FROM @xml.nodes('//r') T(c)

SELECT [ID] 
      ,[Value]
FROM @DataSource

ref:http://stackoverflow.com/questions/20698318/how-to-split-a-string-into-variables-in-sql