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.
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 + '%'
Basically the proper solution is to store the values in a column and serve it in the form of a function or temp table. then treat it like a normal table or view and do whatever type of operation that you wanna do with it.
Here is a good article to perform such thing for your reference:
https://primestudyhub.blogspot.com/comma-separated-values-in-sql
