Char Index

CREATE TABLE #Temp
(
 Name varchar(20)
)

INSERT INTO #Temp
SELECT  'Smith'
union all
SELECT  'Robertson'
union all
SELECT  'Miltson'

SELECT substring(Name,CHARINDEX('s',Name),4) Name  from #Temp