You have used CharIndex wrongly,
Two problem:
1. First parameter is the character you want to find and second is the string
2. You don't need to do -1
Use following modified query.
select SUBSTRING('city,sate',0,CHARINDEX(',','city,sate',0))
select SUBSTRING('city,sate',
(CHARINDEX(',','city,sate',0)+1),(lEN('city,sate')-(CHARINDEX(',','city,sate',0))))
Hope that helps. If it does, mark answer as solution and/or upvote.
Thanks
Milind