Skip to content
Advertisement

Tag column values contained in other rows

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 
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement