Drop the last character of string, if the last character is s
It seems a bit odd issue but here's how to do it.
DECLARE @string NVARCHAR(10)
SET @string = 'Tests'

SELECT
  CASE
    WHEN RIGHT(@string,1) IN ('s','S') THEN SUBSTRING(@string,1,LEN(@string)-1)
    ELSE @string
  END AS string