To Split the column values

Table1 
Value   String 
1          Cleo, Smith 

 

Select

 Value  ,

 Substring(FullName, 1,Charindex(',', FullName)-1) as Name, 
 Substring(FullName, Charindex(',', FullName)+1, LEN(FullName)) as  Surname 
from Table1

 

 

Output 
Value  Name   Surname 
1        Cleo     Smith