CODEWITHSUNDEEP
sqlarrayspostgresqlcsv
Anup
Anup

Reputation: 1712

postgresql searching by string array

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?

Upvotes: 1

Views: 140

Answers (1)

user330315
user330315

Reputation:

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']

Upvotes: 2

Related Questions