How to make "String like any "%element_in_array%"

Question

I have a string like "Caio;Lara;Dani;Jorge".

I want to make a query that makes:

SELECT * FROM Cliente WHERE nome LIKE '%Caio%' 
                         OR nome LIKE '%Lara%' 
                         OR nome LIKE '%Dani%' 
                         OR nome like '%Jorge%';

But I don't know the values that will come from the string. I tried to transform it into an array, but I didn't find a method that compares the string with any of the element in an array.

Answer 1

First; split your ',' separated field in a table using this function:

CREATE FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
    )
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL,
    [value] NVARCHAR(MAX)
    )
AS
BEGIN
    DECLARE @value NVARCHAR(MAX),
        @pos INT = 0,
        @len INT = 0

    SET @string = CASE 
            WHEN RIGHT(@string, 1) != @delimiter
                THEN @string + @delimiter
            ELSE @string
            END

    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
    BEGIN
        SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
        SET @value = SUBSTRING(@string, @pos, @len)

        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
    END

    RETURN
END

However in SQL-Server 2016 and above you can use the function: STRING_SPLIT() You can review it on this site

Then you can join your table with the results table:

select * FROM Cliente
INNER JOIN 
(
    SELECT Value 
    FROM dbo.[fn_split_string_to_column]('Caio;Lara;Dani;Jorge',';') 
)tb
ON name LIKE '%' + Value + '%'