I have the following table in a postreSQL database (simplified for clarity):
| serverdate | name | value
|-------------------------------------
0 | 2019-12-01 | A LOC 123 DISP | 1
1 | 2019-12-01 | B LOC 456 DISP | 2
2 | 2019-12-01 | C LOC 777 DISP | 0
3 | 2019-12-01 | D LOC 000 DISP | 10
4 | 2019-12-01 | A LOC 700 DISP | 123
5 | 2019-12-01 | F LOC 777 DISP | 8
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:
['A_123', 'F_777'] # this is an example only
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):
| serverdate | name | value
|-------------------------------------
0 | 2019-12-01 | A LOC 123 DISP | 1
5 | 2019-12-01 | F LOC 777 DISP | 8
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?
SELECT * FROM
(SELECT serverdate, substring(name from 1 for 1)||'_'||
regexp_replace(name, 'D*', '', 'g') AS name, value
FROM t) j
WHERE name IN('A_123', 'F_777');
Or using a CTE
WITH j AS (
SELECT serverdate, substring(name from 1 for 1)||'_'||
regexp_replace(name, 'D*', '', 'g') AS name2,
value,name
FROM t
) SELECT serverdate,name,value FROM j
WHERE name2 IN('A_123', 'F_777');
serverdate | name | value
------------+----------------+-------
2019-12-01 | A LOC 123 DISP | 1
2019-12-01 | F LOC 777 DISP | 8
(2 Zeilen)