id name marks
1 sh 20,50,40,63
2 an 20,50,90,93
3 is 20,70,40,63
4 an 20,60,40,93
4 ri 20,40,60,73
i need below result which query do i need to use
id name marks marks2
1 sh 20 50
2 an 20 50
3 is 20 70
4 an 20 60
4 ri 20 40
What I have tried:
select
PATINDEX('%,%',marks),
LEFT(marks , PATINDEX('%,%',marks) - 1),
STUFF(marks, 1, PATINDEX('%,%',marks), '')
from xyz