CODEWITHSUNDEEP
postgresqlplpgsql
Md. Parvez Alam
Md. Parvez Alam

Reputation: 4596

Pg sql use ilike operator to search text in array

I have query like

_search_text := 'ind'

select * from table where (_search_text ILIKE ANY (addresses))

The st.addresses have value like [india,us,uk,pak,bang]

It should return each item rows where any item of column addresses contains the string _search_text,

Currently it returns only if give full india in _search_text.

What should I make the change

I was also try to thinkin to use unnest, but since it wil be a sub clause of a very long where cluase... so avoid that.

Thanks

Upvotes: 1

Views: 1932

Answers (3)

clamp
clamp

Reputation: 3262

ANY will not work here because the arguments are in the wrong order for ILIKE. You can define a custom operator to make this work. But this will most likely suffer from poor performance:

create table addresses(id integer primary key, states varchar[]);
insert into addresses values (1,'{"belgium","france","united states"}'),
                             (2,'{"belgium","ireland","canada"}');

CREATE OR REPLACE FUNCTION pg_catalog."rlike"(
    leftarg text,
    rightarg text)
    RETURNS boolean
    LANGUAGE 'sql'
    COST 100
    IMMUTABLE STRICT PARALLEL SAFE SUPPORT pg_catalog.textlike_support

AS $BODY$

SELECT pg_catalog."like"(rightarg,leftarg);

$BODY$;

ALTER FUNCTION pg_catalog."rlike"(text, text)
    OWNER TO postgres;

CREATE OPERATOR ~~~ (
    leftarg = text,
    rightarg = text,
    procedure = rlike
);

select * from addresses where 'bel%'::text ~~~ ANY (states);

It should be possible to define this function in C to make it faster.

Upvotes: 0

Rabhi salim
Rabhi salim

Reputation: 506

ilike ignores cases (difference in upper or lower cases), it doesn't search for string containing your value.

In your case you can use:

_search_text := '%ind%'

select * from table where (_search_text ILIKE ANY (addresses))

Upvotes: 0

Pavel Stehule
Pavel Stehule

Reputation: 45910

I afraid so it is not possible - LIKE, ILIKE supports a array only on right side, and there is searching pattern. Not string. You can write own SQL function:

CREATE OR REPLACE FUNCTION public.array_like(text[], text)
 RETURNS boolean
 LANGUAGE sql
 IMMUTABLE STRICT
 AS $function$
   select bool_or(v like $2) from unnest($1) v
 $function$

and the usage can looks like:

WHERE array_like(addresses, _search_text)

So the readability of this query can be well. But I afraid about performance. There cannot be used any index on this expression. It is result of little bit bad design (the data are not normalized).

Upvotes: 2

Related Questions