function to delete extra spaces between words

[marist89]

Why would you want to? If you need to update values, just call the function:

update test
  set remarks = remove_redundant_spaces (remarks);

[roobaron]

I would be interested on the performance of this function,
my gut feeling is it will be a shocker... even if you create a function based index (with the right version)

We had a developer use a Java function within Oracle(8i) to do something similar ... comparing strings and saw a dramatic improvement over PL/SQL method.

I would be inclined to make sure to fix the input checking before it gets into the database. Or if you need to do a large cleanup: dump the table to text, use perl and reload the table, especially if the table is very,very big.

The reality is that some things were not meant to run in a database. And when it comes the text manipulation PERL kicks arse.

Have Fun

[leonard905]

Thanks, the function works well. The table is not a big about 2k rows.