用户定义函数，用于确定字符串是否包含子字符串

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.

我有这个代码，无论字符串是否包含子字符串，当前返回0。如果找到子字符串，它应该返回1。

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?

我也尝试在我的IF声明中使用charindex无济于事。我错过了一些简单的东西吗？

Testing like so:

测试如下：

SELECT dbo.checkletters('dLHLd');

3 个解决方案

#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.

使用（@MESSAGE VARCHAR（max））作为输入参数。或者代替max指定长度，目前在你的函数中它只有1.这就是问题。

#2

Modify your function as given below:

修改您的功能如下：

ALTER FUNCTION dbo.checkLetters (@MESSAGE VARCHAR(max))
RETURNS INTEGER
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
   DECLARE @value INTEGER;
   DECLARE  @ExpressionToFind VARCHAR(50)
   SET @ExpressionToFind = 'findme'
   IF @MESSAGE  LIKE '%' + @ExpressionToFind + '%'
    SET @value = 1
ELSE
    SET @value = 0
  RETURN @value
END;

The problem was in your input parameter.

问题出在输入参数中。

#3

Your function could be rewritten as:

您的功能可以重写为：

CREATE FUNCTION dbo.checkLetters (@MESSAGE VARCHAR(MAX))
RETURNS BIT
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
    RETURN CAST(CHARINDEX('findMe', @MESSAGE) AS BIT);
END

CHARINDEX will find position of @MESSAGE in given string and if it's higher than 1, it will output 1 (bit operator).

CHARINDEX将在给定的字符串中找到@MESSAGE的位置，如果它高于1，它将输出1（位运算符）。

#1