PL/SQL data formatting

Hello!

I am trying to develop a PL/SQL function to perform some data cleansing. One of the data cleansing requirements is If there are instances of more than 1 space together, the extra spaces should be removed.

Say for example, if my data comes in as "123 456 789", then my function should clean it as "123 456 789" replacing multiple spaces with just 1 space.

What PL/SQL functions could I use to accomplish this?

Thanks
Krish

replace, loop replacing double space with single untill no more replaces are made.

Thanks. But how do I determine if there are no more replaces. I'd appreciate it if you could you send me sample code, as I am new to PL/SQL.

Krish

Well...Did it the hard way. I wrote code to go through the data character by character and remove extra spaces.

Krish

5 replaces should deal with almost all (haven't fully checked out the math) occurances of multiple spaces and replace with a single space. The inner replace matches for 5 spaces, next one 4 spaces, 3 spaces, 2 spaces, 2 spaces. e.g

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(:value_with_spaces)), ' ', ' '),' ', ' '), ' ', ' '), ' ', ' '), ' ', ' ')

(the trims aren't necessary but remove any leading or trailing spaces)
This deals with every number of spaces I've tried it with, but as I said I'm no mathematician.

Andrew

the posting prog removed my double spaces from the above example!!
The numbers in the description are how many spaces should be in each one.