Substring and Character Position

String fucntions

declare @name varchar(10) = 'SMITH,JON' 

SELECT SUBSTRING(@name, CHARINDEX(',', @name)+1, LEN(@name)) AS Name

Result:

Name
----------
JON