How about adding delimiters? Avoiding ardy and ocky.

DECLARE @inputValue NVARCHAR(20)

DECLARE @student TABLE

(Id INT PRIMARY KEY IDENTITY(1,1),

StudentName NVARCHAR(50),

StudentResult INT)

INSERT INTO @student

VALUES( 'Hardy', 100), ('Rocky', 98), ('Panky', 99), ('ardy', 97), ('ocky', 96)

--Select 1

SET @inputValue = 'Hardy'

SELECT * FROM @student WHERE StudentName IN (@inputValue)

--Select 2

SET @inputValue = 'Hardy,Rocky'

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