SQL Server 2005中：字符串分割到数组，数组得到（x）的？ [英] SQL Server 2005 : split string into array and get array(x)?

问题描述

我有一个数据库表中的字符串是这样的：

I have strings in a database table like this:

Peter/Parker/Spiderman/Marvel
Bruce/Wayne/Batman/DC

有没有在SQL的方式从字符串中提取例如特定的值。

Is there a way in SQL to extract specific values from a string e.g.

Name = MyColumn(0)
SurName = MyColumn(1)
Character = MyColumn(3)
Company = MyColumn(4)

感谢

推荐答案

如果你知道你将有完全相同4列，那么你也可以使用这个嵌套的CTE版本：

If you know you will have exactly 4 columns, then you can also use this nested CTE version:

;with s1 (name, extra) as
(
  select left(data, charindex('/', data)-1), 
    substring(data, charindex('/', data) +1, len(data))
  from yourtable
),
s2 (name, surname, extra) as
(
  select name, 
    left(extra, charindex('/', extra)-1), 
    substring(extra, charindex('/', extra)+1, len(extra))
  from s1
),
s3 (name, surname, [character], company) as
(
  select name, 
    surname, 
    left(extra, charindex('/', extra)-1), 
    substring(extra, charindex('/', extra)+1, len(extra))
  from s2
)
select *
from s3;

请参阅 SQL拨弄演示

的结果是：

|  NAME | SURNAME | CHARACTER | COMPANY |
-----------------------------------------
| Peter |  Parker | Spiderman |  Marvel |
| Bruce |   Wayne |    Batman |      DC |

或者你也可以同时实现这会将数据再 PIVOT A CTE：

;with cte (item, data, colNum, rn) as
(
  select cast(left(data, charindex('/',data+'/')-1) as varchar(50)) item,
    stuff(data, 1, charindex('/',data+'/'), '') data,
    1 colNum,
    row_number() over(order by data) rn
  from yourtable
  union all
  select cast(left(data, charindex('/',data+'/')-1) as varchar(50)) ,
    stuff(data, 1, charindex('/',data+'/'), '') data,
    colNum+1, 
    rn
  from cte
  where data > ''
) 
select [1] as Name, 
  [2] as Surname, 
  [3] as [character], 
  [4] as company
from
(
  select item, colnum, rn
  from cte
) src
pivot
(
  max(item)
  for colnum in ([1], [2], [3], [4])
) piv

请参阅 SQL拨弄演示

这篇关于SQL Server 2005中：字符串分割到数组，数组得到（x）的？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！