Re: Determine if range list contains specified integer

On Fri, 27 May 2022 at 18:19, Andrus <kobruleht2(at)hot(dot)ee> wrote:

> Hi!
>
>
> Product type table contains product types. Some ids may missing :
>
> create table artliik (liiginrlki char(3) primary key);
> insert into artliik values('1');
> insert into artliik values('3');
> insert into artliik values('4');
> ...
> insert into artliik values('999');
>
> Property table contais comma separated list of types.
>
> create table strings ( id char(100) primary key, kirjeldLku chr(200) );
> insert into strings values ('item1', '1,4-5' );
> insert into strings values ('item2', '1,2,3,6-9,23-44,45' );
>
> Type can specified as single integer, e.q 1,2,3 or as range like 6-9 or
> 23-44
> List can contain both of them.
>
>
> How to all properties for given type.
> Query
>
> select id
> from artliik
> join strings on ','||trim(strings.kirjeldLku)||',' like
> '%,'||trim(artliik.liiginrlki)||',%'
>
> returns date for single integer list only.
> How to change join so that type ranges in list like 6-9 are also returned?
> Eq. f list contains 6-9, Type 6,7,8 and 9 shoud included in report.
>
> Postgres 13 is used.
>
> Posted also in
>
>
> https://stackoverflow.com/questions/72404218/how-determine-if-range-list-contains-specified-integer
>
> Andrus.
>

Hello,

As Adrian said the best idea would be to redesign your data model. For
example make a third "contains" table where every product/type relationship
is one record. After that your problem would be trivial and your life much
easier.
However, this is a first class place. The customer wants the pain the
customer gets the pain:

Bad idea which solves the immediate problem 1:
Write a function which unpacks your "1,2,3,6-9,4" string into an array
'1','2','3','6','7','8','9','4' and use the string=ANY(array_of_strings)
syntax.

Bad idea which solves the immediate problem 2:
Write a trigger which unpacks the the "1,2,3,6-9,4" form into
"1,2,3,6,7,8,9,4" at insert/update time and then use the the
string=ANY(array_of_strings) syntax.

Regards,
Sándor