How to to check if a column contains words regardless of their order

Rami Jamleh 0
Viewed 35

Table design

  • Table
CREATE TABLE letters(
  id serial2 primary key,
  letterset text
);
  • Insert data
INSERT INTO letters(letterset) 
VALUES
('A'),
('A B'),
('A B C'),
('B'),
('B C'),
('A C'),
('C B'),
('C W A');
  • My solution to finding any order of letters
SELECT * FROM letters
WHERE letterset ~~* '%a%' AND letterset ~~* '%C%';

Question

is there a better way to do this in Postgres

sql postgresql
1 Answers

I am not sure that this would be faster but it remains the same for any number of search letters. First convert letterset and the letters to search for into arrays and then check if the first contains the second.

select * from letters 
where string_to_array(upper(letterset), ' ') @> string_to_array('C,A', ',');
Stefanov.sm0
Related