'FIND_IN_SET' is not a recognized built-in function name.

[HannahVernon]

I'm seeing the following error messages being generated by the BuddyPress Chat module (in /plugins/buddy-chat/public/class-buddy-chat-public.php):

'FIND_IN_SET' is not a recognized built-in function name.

This is the statement generating the error:

SELECT * 
    from wp_bpc_message 
    WHERE (to_id=1 OR (tog_id IN ('') AND from_id != 1)) 
         AND (PATINDEX(','+1, seen_by) < 1 OR FIND_IN_SET(1+',', ','+ seen_by) IS NULL+',') 
    ORDER BY id DESC 

(query is wrapped for readability above, but actually has no CR/LFs in the real code)

I looked in wp-includes/translations.php and can see that FIND_IN_SET should be getting replaced by the translate_findinset($query) function, and you can see that it is getting converted to PATINDEX in the above query for the first instances, however the 2nd instance of FIND_IN_SET is not getting translated.

I was able to temporarily work around the issue by adding the following code to the translate_specific($query) function:

if (stristr($query, "SELECT * from " . $this->prefix . "bpc_message WHERE (to_id=1 OR (tog_id IN ('') AND from_id != 1)) AND (PATINDEX(','+1, seen_by) < 1 OR FIND_IN_SET(1+',', ','+ seen_by) IS NULL+',') ORDER BY id DESC") !== FALSE) {
      $query = "SELECT * from " . $this->prefix . "bpc_message WHERE (to_id=1 OR (tog_id IN ('') AND from_id != 1)) AND (PATINDEX(','+1, seen_by) < 1 OR dbo.FIND_IN_SET(1, seen_by) IS NULL) ORDER BY id DESC";
}

Obviously that is not a permanent workaround, however it has resolved the immediate issue for me.

Also, cheers for a cool product, and you're making me understand php lol.

[HannahVernon]

and, since FIND_IN_SET is not a built-in function, I coded the replacement myself and manually added it to the database. This is the code:

CREATE OR ALTER FUNCTION dbo.FIND_IN_SET
(
    @string_to_find nvarchar(100)
    , @string_to_search nvarchar(4000)
)
RETURNS int
WITH RETURNS NULL ON NULL INPUT, SCHEMABINDING 
AS
BEGIN
    DECLARE @pos int;
    SET @string_to_find = REPLACE(@string_to_find, N',', N'');
    SET @pos = (
        (
        SELECT TOP(1)
              src.[rn]
        FROM (
            SELECT rn = ROW_NUMBER() OVER (ORDER BY (SELECT (NULL)))
                , ss.value
            FROM string_split(@string_to_search, ',') ss 
            ) src
        WHERE src.[value] = @string_to_find
        )
    );

    RETURN @pos;
END;

Since this uses string_split, it will only work on SQL Server 2016+.