Suppose I have the following table as an input :
x
+-----------+--------------+----------------+------------------+
| 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