postgresql searching by string array-CodePudding

We have a table where one column(varchar) contains strings separated by space like below

A.Y A.P F.K
AC.YZ AB.YY
Ap.YZ BC.YZ

I would like to search with A.P A.Y F.K and result should return first row.

I am trying to find a way to split by space and sort and compare the resulting array with the input which is also sorted after splitting in same way.

There is a function string_to_array(<input/column>, ' ') that can be used to split but couldn't find any way to sort the resulting string array. Any Idea?

CodePudding user response：

You don't need to sort the array, you can use the contains operator @>

where string_to_array(the_column, ' ') @> array['A.P', 'F.K', 'A.Y']

This return rows that contain the array on the right hand side, i.e. the column might contain additional elements. e.g. A.P A.Z A.Y F.K would also match.

If you want to find the rows that contain exactly those three elements you can use the contains operator in both directions:

where string_to_array(the_column, ' ') @> array['A.P', 'F.K', 'A.Y']
  and string_to_array(the_column, ' ') <@ array['A.P', 'F.K', 'A.Y']