How to remove irregular spaces by SQL?

[phuongnguyen]

Hi all,
I need you help for a SQL phrase where I can remove irregular spaces in my table if the "blank" > 1.

In TITLE field, the values like this
TITLE
----------------------------------------------------------
AAA AAA AAAA AAAAAA AAA AAAAA A
B BBBBB BBBBBB BBB BBB B
etc.

I want my values in table look like this after updating.
TITLE
----------------------------------------------------------
AAA AAA AAAA AAAAAA AAA AAAA A
B BBBBB BBBBBB BBB BBB B
etc..

Thank you.
phuongnguyen

 

[DanZeMan]

What language are you trying to accomplish this in???
ie: pure SQL, or through a front-end

 

[pn2002]

Hi DanZeMan,
I want to use pure SQL/PSQL to accomplish this, if it's possible. If not, any idea to solve this problem will be appreciate.

Phuong

 

[karluk]

If you want a suggestion to get you started, I would try using the replace function to replace two spaces with a single space. You can set up a loop in pl/sql to iteratively do this until no more double spaces are found.

replace(title, ' ', ' ')

Your exit condition could be when

length(title) = length(replace(title, ' ', ' '))

I'm not saying that this is the best solution, but it seems to me that it would be fairly easy to implement.