Flagging a column name of a query similar to table name

Question

Given a table with a naming scheme as follows:

Example 1:

INFO_APPLICATION_B

CORRESPOND_U|OBJECTIVE_U|APPLICATION_U|DENIED_U|ACCEPTED_U|

Example 2:

INFO_CITIZEN_B

REFUGEE_U|INCOME_U|EDUCATION_U|CITIZEN_U|

I would like to filter out the column name in the table that is similar (as seen in the examples) to the table name. Precisely, in the first example, column number 3 would ideally be flagged due to its similarity with its respective table name. The same idea follows in example 2 where column number 4 would be flagged.

How can I go about doing this in SQL?

I want an output which does not display the columns whose name is similar to the table name:

CORRESPOND_U|OBJECTIVE_U||DENIED_U|ACCEPTED_U|

Notice how "APPLICATION_U" is no longer there because it was similar to the table name "APPLICATION_B".

Answer 1

Loop through _v_relation_column and determine if the column contains the text in the table.

I suspect you mean "similar" in some other way, but I'm just following your example.

create or replace find_similar_columns(varchar(any))
    returns varchar
    language nzplsql
    begin_proc
        declare
            input_table alias for $1;
            included_columns varchar;
            delimiter varchar;
            column record;
            filtered_column varchar;
        begin
            delimiter := '|';
            included_columns := '';
            for column in 
                select 
                    attname 
                from 
                    _v_relation_column 
                where 
                    name = input_table order by attnum
                loop
                filtered_column := replace(column.attname, '\_U', '');
                if input_table not like '%' || filtered_column || '%' then
                    included_columns := included_columns || delimiter || column.attname;
                end if;
            end loop;
        end;
    end_proc;