split string in sql and add each seprareted value into each column of table

Question

Hi i want to split below sting by - and ; and : in sql server and insert into another table

declare @data varchar(max)=null
set @data='nt:865067021846160;2;8.5.05;1,1,20161010102239.000,18.580423,73.815948,549.700,0.28,33.6,11;101;100;0;0;0;FF;146;25}'


col1     col2           col3     col4
Nt  895067021846160     2       7.5.05
nt  875067021846160     3      8.5.05
nt  835067021846160     5      8.5.05

so on

I used substring and CHARINDEX but i am not getting correct result. Thanks

Answer 1

There are many solution available for string splitting requirements, you can use one of string splinting function from here fnSplitString

I just demonstrating how to use this function in your case .

Try to convert all -,; with :

declare @data varchar(max)=null
set @data='nt:865067021846160;2;8.5.05;1,1,20161010102239.000,18.580423,73.815948,549.700,0.28,33.6,11;101;100;0;0;0;FF;146;25}'
SET @data=REPLACE(REPLACE(@data,'-',':'),';',':')

Now Call this fnSplitString as below, and this will gives single column table with all string fragments.

INSERT INTO #TableName(Col1) SELECT * FROM dbo.fnSplitString(@data,':')

This will gives OUTPUT like this

nt
865067021846160
2
8.5.05
1,1,20161010102239.000,18.580423,73.815948,549.700,0.28,33.6,11
101
100
0
0
0
FF
146
25}