Check if CSV string column contains desired values

Question

I am new to PostgreSQL and I want to split string of the following format:

0:1:19

with : as delimiter. After split, I need to check if this split string contains either 0 or 1 as a whole number and select only those rows.

For example:

Table A

Customer	role
A	0:1:2
B	19
C	2:1

I want to select rows which satisfy the criteria of having whole numbers 0 or 1 in role.

Desired Output:

Customer	role
A	0:1:2
C	2:1

Answer 1

Convert to an array, and use the overlap operator &&:

SELECT *
FROM   tbl
WHERE  string_to_array(role, ':') && '{0,1}'::text[];

To make this fast, you could support it with a GIN index on the same expression:

CREATE INDEX ON tbl USING GIN (string_to_array(role, ':'));

See:

• Can PostgreSQL index array columns?
• Check if value exists in Postgres array

Alternatively consider a proper one-to-many relational design, or at least an actual array column instead of the string. Would make index and query cheaper.

Answer 2

We can use LIKE here:

SELECT Customer, role
FROM TableA
WHERE ':' || role || ':' LIKE '%:0:%' OR ':' || role || ':' LIKE '%:1:%';

But you should generally avoid storing CSV in your SQL tables if your design would allow for that.