User Defined Function to determine whether string contains a substring

Question

I have this code which is currently returning 0 regardless if the string contains the substring or not. It should return 1 if the substring is found.

CREATE FUNCTION dbo.checkLetters (@MESSAGE VARCHAR)
RETURNS INTEGER
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
    DECLARE @value INTEGER;
    IF @MESSAGE LIKE '%findMe%'
        SET @value = 1
    ELSE
        SET @value = 0
    RETURN @value
END;

I also tried using charindex in my IF statement to no avail. Am I missing something simple here?

Testing like so:

SELECT dbo.checkletters('dLHLd');

Answer 1

use (@MESSAGE VARCHAR(max)) as a input parameter. or instead of max specify the length, currently in your function it is only 1. That is the issue.