Postgres Query to compare fields and fetch data accordingly

Question

Need help to create query to compare the data and fetch relevant data from table:

Input

Field1	Field2
abc_ID	ID
abc	abc
abc_id_test	test
abc_id_test	abc test
abc_id_test_scenario	scenario
abc_id_test_scenario	cde test

Output

Field1	Field2
abc_id_test_scenario	cde test

If field 2 contains string that matches with field 1 then I need to filter those records.

I don't understand this: "If field 2 contains only 1 word and it matches with field 1 words then I need to filter it." Your result has one row only and there field2 has two "words" (even this is already an assumption, you didn't tell us what you mean by "word"). Why should the row with field2 = "abc test" not appear in the result? Please put much more effort in your question and explain in detail the logic that should be applied. — Jonas Metzler
– Jonas Metzler, Commented Feb 16, 2023 at 13:39

SelVazi · Accepted Answer · 2023-02-16 14:48:03Z

1

You can do it using unnest and string_to_array combined to expand a string to a set of rows. then check rows from Field2 not match Field1

with cte as (
  select Field1, Field2, unnest(string_to_array(Field2, ' ')) as item
  from mytable
)
select field1, Field2
from cte
where Field1 not like concat('%',item,'%')

Demo here

answered Feb 16, 2023 at 14:48

SelVazi

16.2k2 gold badges20 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Query to compare fields and fetch data accordingly

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related