Searching for text with wildcards as a where condition

Question

I have a table named "Reports"

+----------+--------+-----------------------------------------+--------+
| ReportID | userID |               ReportName                | Active |
+----------+--------+-----------------------------------------+--------+
|       15 |    123 | Prefix.DumbReport.1234                  |      0 |
|       16 |    123 | Prefix.DumbReport.2345                  |      0 |
|       17 |    123 | Prefix.ADifferentReport.22              |      0 |
|       18 |    123 | Prefix.SuffixesAndPrefixesAreUnreliable |      0 |
|       19 |    123 | ThisReportisActive.2345                 |      1 |
+----------+--------+-----------------------------------------+--------+

And I have table named "Tasks"

+--------+--------+---------------------+
| TaskID | userID |   Task_Parameters   |
+--------+--------+---------------------+
|      1 |    123 | Prefix.DumbReport   |
|      2 |    123 | SuffixesAndPrefixes |
+--------+--------+---------------------+

I want to find a list of reports that are inactive, and don't have a task associated with them.

You can use not exsts and pattern matching:

select r.*
from reports r
where
    r.active = 0
    and not exists (
        select 1 from tasks t where r.reportName like concat(r.task_parameter, '%')
    )

If it matters, I'm skipping a step for the sake of clarity, but Task_Parameters is actually json, and I'm using SUBSTRING and CHARINDEX to get just the ReportName.

So in this example, I want my query to return Report ID 17 only.

The text contained in Task_Parameters for TaskID 1 is contained in ReportID 15 and 16.
The text contained in Task_Parameters for TaskID 2 is contained in ReportID 18
Report ID 19 has Active = 1, and I don't even know why I included this as an example, because it's just a simple "Active = 0" but I'm not reformatting my tables

I feel like this should be simple, but I'm getting myself all mixed up for how to query that.

user_10676716 · Answer

I want to find a list of reports that are inactive, and don't have a task associated with them.

You can use not exsts and pattern matching:

select r.*
from reports r
where
    r.active = 0
    and not exists (
        select 1 from tasks t where r.reportName like concat(r.task_parameter, '%')
    )

user_4893753 · Answer

You could LEFT JOIN on reports.report_name LIKE '%' || tasks.task_parameters || '%' and then find the rows where active = 0 and task_parameters is null

check out: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=5da318fdeb016434a8b47c5c1df5e2dd

the example uses postgres 12, but any rdbms should be able to handle this

Searching for text with wildcards as a where condition

2 Answers