MySQL Forums
Forum List  »  Stored Procedures

Find Which Columns Have a Given Character in the Value
Posted by: Gregory Hansen
Date: August 23, 2012 11:32AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Find Which Columns Have a Given Character in the Value
2437
August 23, 2012 11:32AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.