SQLite: Matching All Items Within a List of Characters

Question

I am working with a table (essentially a list of words, i.e. a dictionary'), and had the need to create a subset of items from that table that only contained 3 certain characters, let's say, 'A', 'P', and 'Z'.

Obviously, I can use GLOB or LIKE, like so:

WHERE Word LIKE '%A%' AND Word LIKE '%P%' AND Word LIKE '%P%'

...but I seem to recall once doing a similar thing with a single GLOB statement, although I cannot locate the code where I did that. After a search of the web, and even a consultation with ChatGPT (which informs me that it's not possible, for whatever that's worth!), I thought I'd ask here.

I attempted various combinations of GLOB patterns using NOT and the ^ negation character, all to no avail, although I seem to recall that this is how I once successfully tackled the problem.

Chris Catignani · Accepted Answer · 2024-04-17 22:40:29Z

0

And, would you know it, I just recalled the project where I did this, but I'm leaving the question up as it's probably interesting to somebody!

SELECT Word 
FROM Words 
WHERE NOT 'ZAP' GLOB '*[^' || Word || ']*';

Basically, swap the 'search in' term with the 'search for' term, and use NOT with the ^ operator.

I knew I wasn't going crazy!

edited Apr 17, 2024 at 22:40

Chris Catignani

5,30819 gold badges47 silver badges54 bronze badges

answered Apr 11, 2024 at 15:29

ColinE66

11 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLite: Matching All Items Within a List of Characters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related