[sqlite] Searching a table of patterns

[Simon Slavin]

On 28 Mar 2016, at 1:35am, David Rayna  wrote:

> One thing I have occasionally desired is be able to search a table of 
> patterns given a string that might match some rows.
> This is the reverse of searching a table of strings to find ones that match a 
> pattern.

One usually sees

SELECT id FROM myTable WHERE columnName LIKE '%abc%'

but SQLite also handles

SELECT id FROM myTable WHERE 'abc' LIKE columnName

And put strings like '%abc%' in the column of the table.

Simon.

[sqlite] Searching a table of patterns

[Richard Hipp]

On 3/27/16, David Rayna  wrote:
> One thing I have occasionally desired is be able to search a table of
> patterns given a string that might match some rows.
> This is the reverse of searching a table of strings to find ones that
> match a pattern.

CREATE TABLE t1(a INT, pattern TEXT);
INSERT INTO t1(a,pattern) VALUES(123,'x%y');

SELECT a FROM t1 WHERE 'xyzzy' LIKE pattern;

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Searching a table of patterns

[David Rayna]

One thing I have occasionally desired is be able to search a table of 
patterns given a string that might match some rows.
This is the reverse of searching a table of strings to find ones that 
match a pattern.
I was wondering if anyone else has come across this and if a small 
change to sqlite might make it possible.
In a few cases both at the same time would have been useful (find rows 
where pattern in column A matches value X and value in column B matches 
pattern Y).