-1

I am looking to find what unique symbols are used in values of 2 table columns and I'd like to extract them in a .csv format.

I have a table with lots of names and codes and I'd like to see each unique character ever used for each column printed once on a new row.

For example, the dataset is:

    _____________________
   | column 1 | column 2 |
    ---------------------
   | value  1 | value  2 |
    ---------------------

The CSV would look as follows when opened in Excel (ordering of the symbols isn't crucial):

column 1, column 2
a         a
e         e
l         l
u         u
v         v
1         2

I know how to extract rows from a table, but not how to parse values and extract and orgranize unique characters in a csv. Does anyone know of a solution and if this can be done with SQL?

I was thinking of extracting the needed dataset in SQL and then using a different language, populating contents into string variables, converting strings to arrays of individual symbols, and then printing out the unique ones in two columns and saving this back in .csv format.

RogueWizard
  • 1
  • 3

1 Answers1

0

Solved with a nested while loop and substring() function. Having exported columns I was interested in into a new source_table to get sequential IDs, I then (using the procedure) picked out every symbol from every row in a column from source_table and inserted only unique symbols into an export target_table.

drop procedure if exists sample_procedure; 

delimiter #
create procedure sample_procedure()

begin 
declare v_row_count int default 0;
declare v_sym_count int default 0;
declare v_iterate int default 1;
declare v_total int default 0;

set v_total = (select count(id) from source_table);

while v_row_count < v_total do

    set v_row_count = v_row_count + 1;
    set v_sym_count = 0;
    set v_sym_count = (select length(column) from source_table where id = v_row_count);
    set v_iterate = 1; 

    while v_iterate <= v_sym_count do

        insert into target_table (symbol)
            select substring(column, v_iterate, 1) 
            from source_table
            where id = v_row_count
        and not exists (select symbol 
                        from target_table 
                        where symbol in (select substring(column, v_iterate, 1) 
                                         from source_table
                                         where id = v_row_count)
                        );

        set v_iterate = v_iterate + 1;

    end while;
end while;

commit;

end #;

Did this for other columns and then exported symbols from target_table.

RogueWizard
  • 1
  • 3