Suppose I have the following table as an input :
+-----------+--------------+----------------+------------------+ | tableName | duplicate_Id | Index_name | Column_List | +-----------+--------------+----------------+------------------+ | foo | 1 | foo_index_1 | fafa, fifi, fufu | | foo | 2 | foo_index_2 | fafa, fifi | | bar | 1 | bar_index_1 | bubu, bebe | | bar | 2 | bar_index_2 | bubu | | bar | 3 | bar_index_3 | bebe | +-----------+--------------+----------------+------------------+
I want to know for each row of each table if its Column_List value is contained within another row of that same table (Is considered contained if the pattern matchs X%, not only %X%).
As a desired output I would get :
+-----------+--------------+----------------+------------------+-----------+ | tableName | duplicate_Id | Index_name | Column_List | Contained | +-----------+--------------+----------------+------------------+-----------+ | foo | 1 | foo_index_1 | fafa, fifi, fufu | false | | foo | 2 | foo_index_2 | fafa, fifi | true | (true, contained within id 1 of same table) | bar | 1 | bar_index_1 | bubu, bebe | false | | bar | 2 | bar_index_2 | bubu | true | (true, contained within id 1 of same table) | bar | 3 | bar_index_3 | bebe | false | (false, 'bebe%' doesn't match 'bubu,bebe' +-----------+--------------+----------------+------------------+-----------+
I would really appreciate some help or clues here.
Advertisement
Answer
With EXISTS
and the operator LIKE
:
select t.*, case when exists ( select 1 from tablename where Column_list like t.Column_list + ',%' ) then 'true' else 'false' end Contained from tablename t
I assume that you want to match only the starting of the column.
See the demo.
Results:
> tableName | duplicate_Id | Index_name | Column_List | Contained > :-------- | -----------: | :---------- | :--------------- | :-------- > foo | 1 | foo_index_1 | fafa, fifi, fufu | false > foo | 2 | foo_index_2 | fafa, fifi | true > bar | 1 | bar_index_1 | bubu, bebe | false > bar | 2 | bar_index_2 | bubu | true > bar | 3 | bar_index_3 | bebe | false