CODEWITHSUNDEEP
sqlarrayspostgresql
dev.pt
dev.pt

Reputation: 365

Postgresql: Searching within arrays for an arbitrary number of inputs

I have a record in my table (table1) whose name is "Jonh Wood Doe Smith" and I want to return it even if the user types any of the possible combinations: "John Doe", "Jonn Wood Smith", etc

I implemented a collumn (fullName) which is an array with all the names and was thinking to search it like: 

SELECT * FROM table1 WHERE ({"Wood","Doe"}) IN ANY (fullName)

Do you know if this is possible and/or what is the best approach to this kind of problem? I will use postgresql so proprietary methods, functions, etc is not a problem. It doesn't need to be a method compatible with other DBs.

Note: fullName is a collumn like this {"John", "Wood", "Doe", "Smith"}

Upvotes: 0

Views: 64

Answers (1)

Jakub Kania
Jakub Kania

Reputation: 16487

You should use array operator <@ like this:

SELECT * FROM users WHERE array['Wood','Doe'] <@ fullname;

fiddle

But what you really should do is get interested in full text search

Upvotes: 1

Related Questions