function to delete extra spaces between words

Hi gang,
Does anyone know of any oracle function that I can use to delete extraneous spaces between words in a string. For example, the input string 'this is a test data end here'.



Rtrim and Ltrim will not delete between words!

Thanks,
Leonard905

Even I, if you give me that piece of text I wouldnt know which space do I have to delete?

'this is a test data end here'

which space has to be deleted?

I don't think it can be done simply by combining built-in SQL functions. The easiest way is to built your own PL/SQL function taht will remove redundat spaces. The following one is replacing two consecutive spaces with a single space until there is no more "double spaces" in the string.
[code]
create or replace function remove_redundant_spaces (p_instr in varchar2)
return varchar2 is
v_outstr varchar2(4000) := p_instr;
v_length_before number := 0;
v_length_after number := length(v_outstr);
begin
while v_length_before != v_length_after loop
v_length_before := v_length_after;
v_outstr := REPLACE(v_outstr, ' ', ' '); -- replace two blanks with single blank
v_length_after := length(v_outstr);
end loop;
return v_outstr;
end;
/
[code]

Quote:

---

Originally posted by pando
Even I, if you give me that piece of text I wouldnt know which space do I have to delete?

'this is a test data end here'

which space has to be deleted?

---

Pando, you have to look at the HTML source (or click "quote" on the original post) to see there were many spaces between individual words.....

if it was to replace two blanks you could use replace()

Code:

---

select dname from dept;

DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS
las  lala

select replace(dname, '  ',' ') "removing extra space" from dept:

removing extra
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS
las lala

---

thanx jmodic I didnt know I had to read in html ;)

The entry was automatically reset by the browser, maybe. Here it is again:

'this is a data test end here'


Thanks,
Leonard

Quote:

---

Originally posted by pando
if it was to replace two blanks you could use replace()
select replace(dname, ' ',' ') "removing extra space" from dept:

---

This only works if there are exactly 2 blanks between the two words (like in your case between 'las' and 'lala'). What if there are 3 consecutive blanks between two words? Or 15? or 37? .... You have to do it in a loop.

Quote:

---

Originally posted by jmodic
I don't think it can be done simply by combining built-in SQL functions

---

And here I was waiting for this elaborate SQL statement using DECODE, CASE, REPLACE, etc... ;)

YEa I did so because I readextra space not extra spaceS :D

Can I request more help here: Below is my table from which the data comes:


SVRMGR> desc test
Column Name Null? Type
------------------------------ -------- ----
REMARKS VARCHAR2(500)
SVRMGR>

How I can I customize the function (previous thread) to be used on this table?

Thanks,
Leonard905