SQL Split function Two example

Split and then BULK INSERT

DECLARE @s varchar (8000), @sql nvarchar (4000)
Set @s= ' 1,12,1212,4545 '
Set @sql = ' INSERT into T (col) select ' +replace (@s, ', ', ' Col union ALL Select ')
EXEC (@sql)

Test
drop table #table
DECLARE @s varchar (8000), @sql nvarchar (4000)
Set @s= ' 1,12,1212,4545,454 '
CREATE TABLE #table (col int)
Set @sql = ' INSERT into #table (col) select ' +replace (@s, ', ', ' Col union ALL Select ')
EXEC (@sql)
SELECT * FROM #table

Create function F_split (@sourcesql varchar (8000), @strseprate varchar (10))
Returns @temp table (a varchar (100))
--function to realize split function
As
Begin
DECLARE @i int
Set @sourcesql =rtrim (LTrim (@sourcesql))
Set @i=charindex (@strseprate, @sourcesql)
While @i>=1
Begin
Insert @temp values (left (@sourcesql, @i-1))
Set @sourcesql =substring (@sourcesql, @i+1,len (@sourcesql)-@i)
Set @i=charindex (@strseprate, @sourcesql)
End
If @sourcesql <> '
Insert @temp VALUES (@sourcesql)
Return
End

Usage: SELECT * from Dbo.f_split (' Abc:bc:c:d:e ', ': ')

Look at one more example

Create function [dbo]. [Split]
(
@str as nvarchar (1000),
@delimiters as nvarchar (100)
)
Returns @t table
(
POS int NOT NULL,
Value nvarchar (m) NOT NULL
)
As
Begin
DECLARE @end int,
@start int,
@pos int,
@delino int,
@strchar nvarchar (1),
@delimiter nvarchar (1)

Select @str =replace (@str, ', '), @delimiters =replace (@delimiters, ', ')
Set @str = LTrim (RTrim (@str)) +substring (@delimiters, 1, 1)

Select @pos = 1, @start = 1, @end = 1

While @end <= Len (@str)
Begin
Select @strchar = substring (@str, @end, 1),
@delino = 1

While @delino <= Len (@delimiters)
Begin
Set @delimiter = substring (@delimiters, @delino, 1)
If @strchar = @delimiter
Begin
If substring (@str, @start, @end-@start) <> '
Begin
INSERT INTO @t
VALUES (@pos, substring (@str, @start, @end-@start))
Set @pos = @pos + 1
End
Set @start = @end + 1
Break
End
Set @delino = @delino + 1
End
Set @end = @end + 1
End
Return
End
Go