insert into table with split the string in SQL

问题: I want to insert my split string into my table as you can see : create table #Organization ( organizationId bigint, provienceId bigint, CityId bigint,...

问题:

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 :

enter image description here


回答1:

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

回答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.


回答3:

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 
  • 发表于 2019-01-17 16:20
  • 阅读 ( 305 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除