Is there a way to query postgres to obtain all records that have a field that CONTAINS text from a s-CodePudding

I'm trying to search a field that contains information from another table. I've tried the following queries:

SELECT * 
FROM table_1 
WHERE text_field LIKE '%'||(SELECT other_text FROM table_2)||'%';

SELECT * 
FROM table_1 
WHERE text_field ~* '(SELECT other_text FROM table_2)';

But unfortunately I get the ERROR: more than one row returned by a subquery used as an expression return.

Example tables: table_1

id	timestamp	text_field
100	2022-06-01 17:40:00	Two Transactions completed in 12 seconds
101	2022-06-01 17:42:42	One Transaction completed in 5 seconds
102	2022-06-02 03:24:23	15 Records created and 4 deleted in 94 seconds

table_2

id	other_text
1	94
2	12

And I want to query table_1 based on text_field containing either 12 or 94, which would give me this return:

id	timestamp	text_field
100	2022-06-01 17:40:00	2 Transactions completed in 12 seconds
102	2022-06-02 03:24:23	15 Records created and 4 deleted in 94 seconds

I've looked at multiple suggestions but they all center around a JOIN, but my text_field won't ever EQUAL my other_text field.
I've looked at arrays, but each entry in table_2 is distinct, not a comma-delimited list. Each text_field entry has the potential to be a varying length as indicated in my example, so I can't just select the 6th 'space-delimited' field in text_field; I have to do some version of "contains" or "like".

Is this even possible?

CodePudding user response：

Use a CTE(Common Table Expression).

WITH ot as(
   SELECT other_text FROM table_2)
SELECT * 
FROM ot, table_1 
WHERE text_field LIKE '%'|| ot.other_text||'%';

To be complete I should mention you can shorten this to:

SELECT * 
FROM table2, table_1 
WHERE text_field LIKE '%'|| table2.other_text||'%';

UPDATE

I prefer the first form as it makes it clearer to me what is going on.