LIKE IN

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

LIKE IN

metanym
Hi,

Is it possible to achieve the effect of combining the LIKE and IN operators?

So for instance if I have tables:

CREATE TABLE names (name TEXT);
INSERT INTO names VALUES ('Alexandra');
INSERT INTO names VALUES ('Rob');

CREATE TABLE matches (match TEXT);
INSERT INTO matches VALUES ('Alex');
INSERT INTO matches VALUES ('Alexander');
INSERT INTO matches VALUES ('Alexandra');
INSERT INTO matches VALUES ('Rob');
INSERT INTO matches VALUES ('Robin');
INSERT INTO matches VALUES ('Robert');

I can query as follows:

SELECT * FROM names WHERE name IN (SELECT * FROM matches);

But can I do something more like:

CREATE TABLE queries (query TEXT);
INSERT INTO queries VALUES ('Alex%*');
INSERT INTO queries VALUES ('Rob%*');

SELECT * FROM table WHERE name LIKE IN (SELECT * FROM queries);

Thanks,
Hamish
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIKE IN

Simon Davies
Hi,

On Fri, 22 Nov 2019 at 13:18, Hamish Allan <[hidden email]> wrote:

>
> Hi,
>
> Is it possible to achieve the effect of combining the LIKE and IN operators?
>
> So for instance if I have tables:
>
> CREATE TABLE names (name TEXT);
> INSERT INTO names VALUES ('Alexandra');
> INSERT INTO names VALUES ('Rob');
>
> CREATE TABLE matches (match TEXT);
> INSERT INTO matches VALUES ('Alex');
> INSERT INTO matches VALUES ('Alexander');
> INSERT INTO matches VALUES ('Alexandra');
> INSERT INTO matches VALUES ('Rob');
> INSERT INTO matches VALUES ('Robin');
> INSERT INTO matches VALUES ('Robert');
>
> I can query as follows:
>
> SELECT * FROM names WHERE name IN (SELECT * FROM matches);
>
> But can I do something more like:
>
> CREATE TABLE queries (query TEXT);
> INSERT INTO queries VALUES ('Alex%*');
> INSERT INTO queries VALUES ('Rob%*');
>
> SELECT * FROM table WHERE name LIKE IN (SELECT * FROM queries);
>
> Thanks,
> Hamish

Will a JOIN not do what you want?

SELECT table.* from table t JOIN queries q ON t.name LIKE q.query;

(after cleaning up query, to 'Alex%' and 'Rob%')

Rgds,
Simon
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIKE IN

David Raymond
Or alternatively something like:

select * from table
where exists (
    select query from queries
    where table.name like query
);

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIKE IN

Gert Van Assche-2
I think this will work:

INSERT INTO queries VALUES ('Alex');
INSERT INTO queries VALUES ('Rob');

select * from names
where exists (
    select query from queries
    where names.name like '%'||query||'%'
);

On Fri, 22 Nov 2019 at 15:19, David Raymond <[hidden email]>
wrote:

> Or alternatively something like:
>
> select * from table
> where exists (
>     select query from queries
>     where table.name like query
> );
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIKE IN

Gert Van Assche-2
Both queries will work like this:

DROP TABLE names;
CREATE TABLE names (name TEXT);
INSERT INTO names VALUES ('Alex');
INSERT INTO names VALUES ('Alexander');
INSERT INTO names VALUES ('Alexandra');
INSERT INTO names VALUES ('Rob');
INSERT INTO names VALUES ('Rhobin'); -- should not match
INSERT INTO names VALUES ('Robert');

CREATE TABLE queries (query TEXT);
INSERT INTO queries VALUES ('Alex');
INSERT INTO queries VALUES ('Rob');

SELECT name from names t JOIN queries q ON t.name LIKE '%'||q.query||'%';

SELECT name from names
where exists (
    select query from queries
    where names.name like '%'||query||'%'
);


On Sat, 23 Nov 2019 at 11:34, Gert Van Assche <[hidden email]> wrote:

> I think this will work:
>
> INSERT INTO queries VALUES ('Alex');
> INSERT INTO queries VALUES ('Rob');
>
> select * from names
> where exists (
>     select query from queries
>     where names.name like '%'||query||'%'
> );
>
> On Fri, 22 Nov 2019 at 15:19, David Raymond <[hidden email]>
> wrote:
>
>> Or alternatively something like:
>>
>> select * from table
>> where exists (
>>     select query from queries
>>     where table.name like query
>> );
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIKE IN

David Raymond
There'll be a few differences.

The JOIN version will return 1 row for every item in queries which matches, and it will test every single one every time. So if you have in the queries table both 'Alex' and 'Alexand' then 'Alexander' and 'Alexandra' will each show up twice, once for 'Alex' and once for 'Alexand'. Depending on what you're doing this may be what you want.

The EXISTS version will only ever return one row for each record in the names table, and it will stop checking other patterns once it finds one that matches.

So if you want any info from the queries table then go with the join route, if you only care if yes/no there's anything at all that matches, then go with exists.



-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Gert Van Assche
Sent: Saturday, November 23, 2019 5:43 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] LIKE IN

Both queries will work like this:

DROP TABLE names;
CREATE TABLE names (name TEXT);
INSERT INTO names VALUES ('Alex');
INSERT INTO names VALUES ('Alexander');
INSERT INTO names VALUES ('Alexandra');
INSERT INTO names VALUES ('Rob');
INSERT INTO names VALUES ('Rhobin'); -- should not match
INSERT INTO names VALUES ('Robert');

CREATE TABLE queries (query TEXT);
INSERT INTO queries VALUES ('Alex');
INSERT INTO queries VALUES ('Rob');

SELECT name from names t JOIN queries q ON t.name LIKE '%'||q.query||'%';

SELECT name from names
where exists (
    select query from queries
    where names.name like '%'||query||'%'
);


On Sat, 23 Nov 2019 at 11:34, Gert Van Assche <[hidden email]> wrote:

> I think this will work:
>
> INSERT INTO queries VALUES ('Alex');
> INSERT INTO queries VALUES ('Rob');
>
> select * from names
> where exists (
>     select query from queries
>     where names.name like '%'||query||'%'
> );
>
> On Fri, 22 Nov 2019 at 15:19, David Raymond <[hidden email]>
> wrote:
>
>> Or alternatively something like:
>>
>> select * from table
>> where exists (
>>     select query from queries
>>     where table.name like query
>> );
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIKE IN

Gert Van Assche-2
Thanks for clarifying this, David. Learned something new today!

On Mon, 25 Nov 2019 at 15:25, David Raymond <[hidden email]>
wrote:

> There'll be a few differences.
>
> The JOIN version will return 1 row for every item in queries which
> matches, and it will test every single one every time. So if you have in
> the queries table both 'Alex' and 'Alexand' then 'Alexander' and
> 'Alexandra' will each show up twice, once for 'Alex' and once for
> 'Alexand'. Depending on what you're doing this may be what you want.
>
> The EXISTS version will only ever return one row for each record in the
> names table, and it will stop checking other patterns once it finds one
> that matches.
>
> So if you want any info from the queries table then go with the join
> route, if you only care if yes/no there's anything at all that matches,
> then go with exists.
>
>
>
> -----Original Message-----
> From: sqlite-users <[hidden email]> On
> Behalf Of Gert Van Assche
> Sent: Saturday, November 23, 2019 5:43 AM
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] LIKE IN
>
> Both queries will work like this:
>
> DROP TABLE names;
> CREATE TABLE names (name TEXT);
> INSERT INTO names VALUES ('Alex');
> INSERT INTO names VALUES ('Alexander');
> INSERT INTO names VALUES ('Alexandra');
> INSERT INTO names VALUES ('Rob');
> INSERT INTO names VALUES ('Rhobin'); -- should not match
> INSERT INTO names VALUES ('Robert');
>
> CREATE TABLE queries (query TEXT);
> INSERT INTO queries VALUES ('Alex');
> INSERT INTO queries VALUES ('Rob');
>
> SELECT name from names t JOIN queries q ON t.name LIKE '%'||q.query||'%';
>
> SELECT name from names
> where exists (
>     select query from queries
>     where names.name like '%'||query||'%'
> );
>
>
> On Sat, 23 Nov 2019 at 11:34, Gert Van Assche <[hidden email]> wrote:
>
> > I think this will work:
> >
> > INSERT INTO queries VALUES ('Alex');
> > INSERT INTO queries VALUES ('Rob');
> >
> > select * from names
> > where exists (
> >     select query from queries
> >     where names.name like '%'||query||'%'
> > );
> >
> > On Fri, 22 Nov 2019 at 15:19, David Raymond <[hidden email]>
> > wrote:
> >
> >> Or alternatively something like:
> >>
> >> select * from table
> >> where exists (
> >>     select query from queries
> >>     where table.name like query
> >> );
> >>
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users