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
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.
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;
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
/in your data? Or will it always have 3?