SQL Like % Inside Array

SQL LIKE % inside array

$sql = array('0'); // Stop errors when $words is empty

foreach($words as $word){
    $sql[] = 'name LIKE %'.$word.'%'
}

$sql = 'SELECT * FROM users WHERE '.implode(" OR ", $sql);

Edit: code for CakePHP:

foreach($words as $word){
    $sql[] = array('Model.name LIKE' => '%'.$word.'%');
}

$this->Model->find('all', array(
    'conditions' => array(
        'OR' => $sql
    )
));

Read up on this stuff: http://book.cakephp.org/1.3/en/view/1030/Complex-Find-Conditions

How to SELECT using both wildcards (LIKE) and array (IN)?

SELECT *
FROM table_name
WHERE field_name LIKE '%one'
   OR field_name LIKE '_two'
   OR field_name LIKE 'three[abv]'

Is there a way to use like operator with an array in HiveQL?

Concatenate array using some delimiter, for example | and use concatenated string in RLIKE operator.

Demo:

with mytable as (
select 1 id, array('8001','12100') as `values`
union all
select 2, array('12134','9999','2222')
union all
select 3, array() 
union all
select 4, array('5671','9765') 
)
select * from mytable
where concat('|',concat_ws('|',`values`),'|') rlike '\\|121'

Result:

id  values
1   ["8001","12100"]
2   ["12134","9999","2222"]

Note: Pipe | in regex needs to be escaped with double backslash.

SQL select everything in an array

SELECT * FROM products WHERE catid IN ('1', '2', '3', '4')

Is there a combination of LIKE and IN in SQL?

There is no combination of LIKE & IN in SQL, much less in TSQL (SQL Server) or PLSQL (Oracle). Part of the reason for that is because Full Text Search (FTS) is the recommended alternative.

Both Oracle and SQL Server FTS implementations support the CONTAINS keyword, but the syntax is still slightly different:

Oracle:

WHERE CONTAINS(t.something, 'bla OR foo OR batz', 1) > 0

SQL Server:

WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')

The column you are querying must be full-text indexed.

Reference:

Building Full-Text Search Applications with Oracle Text
Understanding SQL Server Full-Text

Return rows where array column has match for every pattern in given array

You want a match in the array column value for every LIKE pattern in the given array of matches.

This query is tricky for two main reasons:

There is no array operator to compare a whole array to an array of LIKE patterns. (No "array contains" operator with pattern-matching.) The array column must be unnested.
It's not enough to simply count matches after unnesting, as one pattern can match multiple times, masking the absence of matches for another.

Rephrase the task like this:

"Return all rows where none of the input patterns fails to find a match."

This query implements it, as efficiently as possible:

SELECT t.id, t.value
FROM   tbl t
WHERE  NOT EXISTS (
   SELECT FROM unnest('{%friend%, %cat%}'::text[]) AS p(pattern)
   WHERE  NOT EXISTS (
      SELECT FROM unnest(t.value) AS a(elem)
      WHERE  a.elem LIKE p.pattern
      )
   );

db<>fiddle here

Unfortunately, no index support possible. You'd have to normalize your relational design to allow that - with a many-to-one table replacing the array value.

Asides

Either way, to optimize performance, fork two distinct cases: search with and without special LIKE characters. Just check for the existence of characters with special meaning, i.e. one of \%_. Related:

Escape function for regular expression or LIKE patterns

Your simple query can deal with plain equality - after sanitizing it:

SELECT id, value
FROM   tbl
WHERE  value @> '{friend, cat}';

DISTINCT(id), value was just a misleading, equivalent syntax variant of DISTINCT id, value. Are you confusing this with DISTINCT ON? See:

Select first row in each GROUP BY group?

And, assuming id is the PK, then DISTINCT is just an expensive no-op in the given query. Remove it.

Finally, use text[] rather than varchar[]. There are corner cases where text[] is superior, text being the "preferred" string type. See:

Any downsides of using data type "text" for storing strings?
PostgreSQL ignores index, runs seq scan

How to make String like any %element_in_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 + '%'