CODEWITHSUNDEEP
sqlpostgresql
sagar_c_k
sagar_c_k

Reputation: 123

Check if CSV string column contains desired values

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

Upvotes: 1

Views: 1033

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656754

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.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521269

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.

Upvotes: 0

Related Questions