I am trying to write a query that will tell me all of the columns in a table where at least one of the values in the column has a given character.
The table I'm currently working with has 243 columns. I loaded the data from a tab delimited file created from excel. When Excel found a cell that had a comma or quote character in it, Excel enclosed the enire string in quotes, and in the case of quote characters it escaped the quote with another quote.
For example:
500 - 1,000 became "500 - 1,000"
Ask "the question" became "Ask ""the question"""
Following is the procedure I have created to try to find the columns where at least one value in the column has a quote character.
DELIMITER //
drop procedure quote_column;
CREATE PROCEDURE quote_column()
BEGIN
declare done int default false;
declare col_name varchar(120);
declare q_cnt int default 0;
declare cur1 cursor for
select column_name
from information_schema.columns cs
where cs.table_name = 'raw_survey_data'
and cs.table_schema = 'survey';
declare continue handler for not found set done = true;
open cur1;
read_loop: loop
fetch cur1 into col_name;
if done then
leave read_loop;
end if;
select count(*)
into q_cnt
from survey.raw_survey_data rsd
where concat('rsd.', col_name) like '%"%';
if q_cnt > 0 then
insert into quote_columns(column_name)
values(col_name);
end if;
set q_cnt = 0;
end loop;
close cur1;
select * from quote_columns;
END //
DELIMITER ;
When I call this procedure, I do not get any results.
Thanks,
Gregory