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

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

Code:

---

update test
  set remarks = remove_redundant_spaces (remarks);

---

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

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