Multiple values in single Postgres database table column

Question

I recently took over as db admin for an app I am unfamiliar with. I was poking around the development db instance and found a table with multiple values in a single column. I am not sure how to query on this column as what I have tried thus far has not worked.

I have tried various characters between the column name and what looks like a key value pair in the column value itself. I am trying to select based on the state :name value. None of the below have worked and I have not yet found the postgres doc that addresses this.

select * from district where state = 'California';

select * from district where state:name = 'California';

select * from district where state_name = 'California';

select * from district where state[name] = 'California';

select * from district where state[:name] = 'California';
select * from district where state[0] = 'California';

Below is a specific entry from the table districts

id         | 8
name       | 13
state      | --- + | :name: California + | :abbr: CA + |
created_at | 2011-12-08 04:31:15.104002
updated_at | 2011-12-08 04:31:15.104002

Answer 1

Try a regular expression:

WHERE name  ~ '\mCalifornia\M'