doublespace to a single space

[mkey]

Hi all,

I have a whole bunch of tables that have columns that are varchar. I want to check for any double space within the string. Also for any space in front of the string or at the end of a string.
I know I could check like this:

WHERE (DESCRIPTION LIKE '% %' OR DESCRIPTION LIKE ' %' OR DESCRIPTION LIKE '% 'OR DESCRIPTION LIKE ' %' OR DESCRIPTION LIKE '% ');

But each table have different column names. So its a lot of work to specify each table and its corresponding columns. How can I check for double space a bit faster without writing script for each table?

Thanks!

 

[Bimalaggarwal]

You will have to go over the table ALL_TAB_COLUMNS and write a dynamic script.You can restrict your query over ALL_TAB_COLUMNS to pick up columns of type VARCHAR2 only.Maybe, you will have to pick LONG column types too, depending on your situation.

 

[sem]

Why don't you use instr(field,' ')<>0 ? I agree, you should create the statements dynamically, looping throug ALL_TABLES and ALL_TAB_COLUMNS.
BTW, how do you plan to identify such fields? Regards, Dima

 

[Bimalaggarwal]

As I have mentioned in my previous posting, the fields could be identified using their DATA_TYPE ( and maybe, OWNER as well)