Remove whitespaces/blanks from a string

Hi,

How do I remove blanhs from with a string using a select statement.

For example the tring is 'Ron nie' and I want my select to get 'Ronnie'.

Thanks

SELECT REPLACE('Ron nie', ' ') FROM dual;

Thanks,

Actually I am looking for something where the whitespace is removed only after a comma and not otherwise

Like in this case the blank should not be removed in the statement

'Tom Hanks'

But in this case the blank should be removed
'Tom, Hanks'

Notice the comma here. Hence I am trying to remove blank spaces around a comma and in all other cases it should ignore

Thanks

Code:

---

SQL> SELECT REPLACE('Tom Hanks' , ', ', ',') FROM dual;

REPLACE('
---------
Tom Hanks

SQL> SELECT REPLACE('Tom, Hanks' , ', ', ',') FROM dual;

REPLACE('
---------
Tom,Hanks

SQL>

---

Thanks Jurij,

I want to remove all the occurences of whitespaces after a comma.

This is what i get with the above query

1* SELECT REPLACE('Tom, Hanks' , ',&nbsp ', ',') FROM dual
SQL> /

REPLACE('TOM,&nbsp&nbsp&nbsp&nbspHANKS',',',',')
---------------------------------
Tom,&nbsp&nbsp&nbsp&nbspHanks


It should be

Tom,Hanks


Thanks

I'm affraid you'll have to use PL/SQL for that. Something like the following function:

Code:

---

SQL> create or replace function remove_blanks_after_comma (p_in in varchar2)
  2  return varchar2 as
  3    v_out varchar2(4000) := p_in;
  4  begin
  5    while INSTR(v_out, ', ') > 0 loop
  6      v_out := replace(v_out, ', ', ',');       
  7    end loop;
  8    return v_out;
  9  end;
 10  /

Function created.

SQL> SELECT remove_blanks_after_comma('Tom,  Hanks') FROM dual;

REMOVE_BLANKS_AFTER_COMMA('TOM,HANKS')
--------------------------------------------------------------------------------
Tom,Hanks

SQL> SELECT remove_blanks_after_comma('Tom    Hanks') FROM dual;

REMOVE_BLANKS_AFTER_COMMA('TOMHANKS')
--------------------------------------------------------------------------------
Tom    Hanks

SQL>

---