Skip to content
Advertisement

PostgreSQL search lists of substrings in string column

I have the following table in a postreSQL database (simplified for clarity):

name columns is of type string. The substrings LOC and DISP can have other values of different lengths but are not of interest in this question.

The problem: I want to SELECT the rows that only contain a certain substring. There are several substrings, passed as an ARRAY, in the following format:

I would want to select all the rows that contain the first part of the substring (sepparating it by the underscore ‘_’), as well as the second. In this example, with the mentioned array, I should obtain rows 0 and 5 (as these are the only ones with exact matches in both parts of the):

Row 4 has the first part of the substring correct, but not the other one, so it shouldn’t be returned. Same thing with row 2 (only second part matches).

How could this query be done? I’m relatively new to SQL.

This query is part of process in Python, so I can adjust the input parameter (the substring array) if needed, but the behaviour must be the same as the one described.

Thanks!

Advertisement

Answer

Have you tried with regexp_replace and a subquery?

Or using a CTE

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement