it is because in the sample the delimiter became now comma and space.

both possibilities will work:

SET @inputValue = 'Hardy, Rocky, ardy'

SELECT * FROM @student WHERE CHARINDEX(', '+StudentName+',', ', '+@inputValue+',') > 0

SET @inputValue = 'Hardy,Rocky,ardy'

SELECT * FROM @student WHERE CHARINDEX(','+StudentName+',', ','+@inputValue+',') > 0

HTH

Stefan