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 |
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:
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.
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.
