insert into table with split the string in SQL

Question

I want to insert my split string into my table as you can see :

create table #Organization
(
    organizationId bigint, 
     provienceId bigint, 
     CityId bigint, 
      TownId bigint 

)
Insert Into #Organization  ( organizationId) 
 select  p.value from  string_split('1,2,3', ',') p
Insert Into #Organization  ( provienceId) 
 select  p.value from  string_split('1,2,3', ',') p
Insert Into #Organization  ( CityId) 
 select  p.value from  string_split('1,2,3', ',') p
Insert Into #Organization  ( TownId) 
 select  p.value from  string_split('1,2,3', ',') p

My expected result is something like this :

1   1     1   1
2   2     2   2
3   3     3   3

But it returns this :

Is there only three values every time or this is not real data? — Prashant Pimpale
– Prashant Pimpale, Commented Jan 12, 2019 at 14:07
yes exactly, So can you provide sample data that you are actually dealing with? — Prashant Pimpale
– Prashant Pimpale, Commented Jan 12, 2019 at 14:16

John Cappelletti · Accepted Answer · 2019-01-12 14:47:27Z

3

Perhaps I'm reading into your question, but I suspect you want to split your string into columns

Example

Declare @YourTable table (SomeColName varchar(max)) 
Insert Into @YourTable values 
 ('1,2,3')
,('A,B,C')
,('Dog,Cat,Pony')

Select B.*
 From  @YourTable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(SomeColName ,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

Returns

Pos1    Pos2    Pos3
1       2       3
A       B       C
Dog     Cat     Pony

edited Jan 12, 2019 at 14:47

answered Jan 12, 2019 at 14:16

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ehsan Akbar Over a year ago

if i want to change the 1 2 3 ,4 5 6 , 7 8 9 to 1 4 7 ,2 5 8 , 3 6 9 ,what should i do ?

John Cappelletti Over a year ago

Are you looking for matrix manipulations? Perhaps edit your question and add a more distinct data sample and desired results.

John Cappelletti Over a year ago

@EhsanAkbar Happy to help ... I guess :)

Ehsan Akbar Over a year ago

Hi again ,in fact in my question i expect these result ,1 1 1 1,2 2 2 2 ,3 3 3 3 ,could you please help me how can i get this result?

Ehsan Akbar Over a year ago

i changed my question

|

Gordon Linoff · Accepted Answer · 2019-01-12 14:10:50Z

2

Insert is inserting three rows, one for each value. If you want to combine them into columns, you need to pivot them or (as I prefer) use aggregation.

You also have a table with four columns but only seem to be inserting three, so I suspect you want:

create table #Organization (
    organizationId int identity(1, 1) primary key, 
    provinceId int, 
    CityId int, 
    TownId int 
);

(I don't see a need for bigint for this example.)

Obviously, the simplest solution is not to use strings:

Insert Into #Organization (provinceId, CityId, TownId) 
    values (1, 2, 3);

But if you are using strings, you can try:

Insert Into #Organization (provinceId, CityId, TownId) 
    select provinceId, CityId, TownId
    from (values('1,2,3')) v(str) cross apply
         (select max(case when seqnum = 1 then p.value end) as provinceid,
                 max(case when seqnum = 2 then p.value end) as cityid,
                 max(case when seqnum = 3 then p.value end) as townid                 
          from (select p.*,
                       row_number() over (order by charindex(',' + p.value + ',', ',' + v.str + ',')) as seqnum
                from string_split(v.str, ',') p
               ) p
         ) s;

Note that there is an issue with using string_split() for this purpose, because it does not "remember" the position of the substring in the original string. This attempts to get around this problem by using charindex(). In your case, this will work, because the values are all numbers.

Here is a db<>fiddle.

edited Jan 12, 2019 at 14:10

answered Jan 12, 2019 at 14:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

Prashant Pimpale Over a year ago

Expecting an answer from you!

Ehsan Akbar Over a year ago

Thank you ,but i want to say the 1,2,3 is an example it can be changed to other values

Gordon Linoff Over a year ago

@EhsanAkbar . . . And nothing in this answer precludes that. Did you look at the db<>fiddle? It has examples of other values.

Kevin · Accepted Answer · 2019-01-12 14:31:45Z

0

I would use pivot to transform the columns into rows. Like:

INSERT INTO #organization 
            (provinceid, 
             cityid, 
             townid) 
SELECT * 
FROM   (SELECT p.value, 
               RowN = Row_number() 
                        OVER ( 
                          ORDER BY (SELECT NULL)) 
        FROM   String_split('1,2,3', ',') p) a 
       PIVOT (Max(a.value) 
             FOR rown IN ([1], 
                          [2], 
                          [3])) b

answered Jan 12, 2019 at 14:31

Kevin

2,6311 gold badge14 silver badges12 bronze badges

1 Comment

Ehsan Akbar Over a year ago

Hi again ,in fact in my question i expect these result ,1 1 1 1,2 2 2 2 ,3 3 3 3 ,could you please help me how can i get this result?

Collectives™ on Stack Overflow

insert into table with split the string in SQL

3 Answers 3

6 Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related