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

Possible duplicate of [T-SQL split string based on delimiter](http://stackoverflow.com/questions/21768321/t-sql-split-string-based-on-delimiter) — Lajos Arpad, Oct 12 '16 at 07:23
Here's a fantastic article giving a range of options, along with code & performance tests: https://sqlperformance.com/2012/07/t-sql-queries/split-strings — JohnLBevan, Oct 12 '16 at 07:43

Jaydip Jadhav · Accepted Answer · 2016-10-13T07:15:04.273

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}

edited Oct 13 '16 at 07:15

answered Oct 12 '16 at 07:29

Jaydip Jadhav

12,179
6
24
40

INSERT INTO #TableName SELECT * FROM dbo.fnSplitString(@data,':') it fails column mismatch while inserting below line by line data in specific column – Shridhar Oct 13 '16 at 07:09
#TableName column should have only on column of data type varchar(max) is it? – Jaydip Jadhav Oct 13 '16 at 07:11
but i want to add this records in particular column of table consider my table is create table #tempframedata (col1 varchar(max),col2 varchar(max), col3 varchar(max), col4 varchar(max), col5 varchar(max),col6 varchar(max),col7 varchar(max),col8 varchar(max),col9 varchar(max) ,col10 varchar(max),col11 varchar(max),col12 varchar(max),col13 varchar(max)) – Shridhar Oct 13 '16 at 07:11
and in which column you want to add this data – Jaydip Jadhav Oct 13 '16 at 07:12
1st column 1st row data then next like this – Shridhar Oct 13 '16 at 07:13
Not sure about your business requirement,try updated ans – Jaydip Jadhav Oct 13 '16 at 07:14
actually i want to split this string with above condition and insert data in specific table column – Shridhar Oct 13 '16 at 07:16
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/125563/discussion-between-jaydipj-and-shridhar). – Jaydip Jadhav Oct 13 '16 at 07:17

Unnikrishnan R · Answer 2 · 2016-10-13T09:59:37.370

Try out with the below script.

DECLARE @data VARCHAR(Max) = '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}'
DECLARE @SpitChars varchar(100) = '%[:;-]%'

;WITH cte_1 
  AS
    (
        SELECT PATINDEX(@SpitChars, @data) - 1 + case when  PATINDEX(@SpitChars, Left(@data,1)) = 1 then 1 else 0 end AS LineBreaker
            , @data AS dataString
            ,SUBSTRING(@data, 1, ISNULL(NULLIF(PATINDEX(@SpitChars, @data), 0) - 1 +case when  PATINDEX(@SpitChars, Left(@data,1)) = 1 then 1 else 0 end, 8000)) AS [Extract]
        UNION ALL
        SELECT LineBreaker + ISNULL(NULLIF(PATINDEX(@SpitChars, SUBSTRING(dataString, LineBreaker+1 , 8000)), 0) -  case when  PATINDEX(@SpitChars, SUBSTRING(dataString, LineBreaker+1 , 8000)) = 1 then 0 else 1 end  ,datalength(dataString)-LineBreaker) LineBreaker
            , dataString
            ,SUBSTRING(dataString, LineBreaker+ 1 , ISNULL(NULLIF(PATINDEX(@SpitChars, SUBSTRING(dataString, LineBreaker+1 , 8000)), 0) -  case when  PATINDEX(@SpitChars, SUBSTRING(dataString, LineBreaker+1 , 8000)) = 1 then 0 else 1 end  ,datalength(dataString)-LineBreaker)) AS [Extract]
        FROM  cte_1
        WHERE LineBreaker BETWEEN 1 AND datalength(@data)-1 
    )
SELECT [1] Col1,[2] Col2,[3] Col3,[4] Col4,[5] Col5
      ,[6] Col6,[7] Col7,[8] Col8,[9] Col9,[10] Col10
      ,[11] Col11,[12] Col12,[13] Col13
FROM(
SELECT [Extract],ROW_NUMBER()OVER( ORDER BY (SELECT 1))RNO
FROM  cte_1
WHERE [Extract] NOT IN (':',';','-'))t
PIVOT(MAX([Extract]) FOR [RNO] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13]) )as pvt

is it possible to insert data into specific column other than single column — Shridhar, Oct 13 '16 at 09:23
can you give some more details? do you want to insert the result (split records)in to some other table? — Unnikrishnan R, Oct 13 '16 at 09:26
i just want store data in table like col1 col2 col3 col4 Nt 895067021846160 2 7.5.05 nt 875067021846160 3 8.5.05 nt 835067021846160 5 8.5.05 — Shridhar, Oct 13 '16 at 09:28
But in this sample ,we will get 13 strings as output,when we split using the delimiters ':;-'.so you need 13 columns to store the data.. if there are more strings,how you wanted to manage this? — Unnikrishnan R, Oct 13 '16 at 09:39
use the updated query.. let me know if you needs any clarification. — Unnikrishnan R, Oct 13 '16 at 10:00

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

2 Answers2