SQL Server 2005 : split string into array and get array(x)?

Question

I have strings in a database table like this:

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

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)

Thanks

Answer 1

I'm assuming here that there are always exactly 4 parts.

If so, you could replace / with . and use the nice built in ParseName function. The only issue from your example is that it counts from the end, so you'd need to be careful about which part you want:

DECLARE @test VARCHAR(max);
SET @test = 'Peter/Parker/Spiderman/Marvel';
SET @test = Replace(@test, '/', '.');

SELECT Parsename(@test, 4),--returns Peter
       Parsename(@test, 3),--returns Parker
       Parsename(@test, 2),--returns Spiderman
       Parsename(@test, 1) --returns Marvel

If there are a variable number of parts, you'll need to find a string splitting function to do this for you, there isn't a good one built in. Many options can be found searching SO: https://stackoverflow.com/search?q=[sql+server]+string+split

Word of warning - if you try to use a number with PARSENAME other than 1-4, the result will always be NULL.

Answer 2

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;

See SQL Fiddle with Demo

The result is:

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

Or you can implement both a CTE that splits the data and then a PIVOT:

;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

See SQL Fiddle with Demo