OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Sql help

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sql help [message #7580]

Mon, 23 June 2003 10:46

Shak
Messages: 13
Registered: May 2002

Junior Member

Hello people,
I have a table X with column y of varchar2 type.

$ represents blank spaces in the column
Y
----
REMOVE$$$THE$$$$EXTRA$$$BLANK$$$$SPACES
REMOVE$$$THE$$$$EXTRA$$$BLANK$$$$SPACES$$$WHICH$$$$ARE$$$$$PRESENT

I want to remove the extra blank spaces between each word. I want to get a result like

Y
----
REMOVE$THE$EXTRA$BLANK$SPACES
REMOVE$THE$EXTRA$BLANK$SPACES$WHICH$ARE$PRESENT

Thanks in advance

Report message to a moderator

Re: Sql help [message #7583 is a reply to message #7580]

Mon, 23 June 2003 11:18

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

dbadmin@mutation_mutation > select * from test23;

Y
----------------------------------------------------------------------------------------------------
REMOVE$$$THE$$$$EXTRA$$$BLANK$$$$SPACES
REMOVE$$$THE$$$$EXTRA$$$BLANK$$$$SPACES$$$WHICH$$$$ARE$$$$$PRESENTA

dbadmin@mutation_mutation > select replace(y,'$$') from test23;

REPLACE(Y,'$$')
----------------------------------------------------------------------------------------------------
REMOVE$THEEXTRA$BLANKSPACES
REMOVE$THEEXTRA$BLANKSPACES$WHICHARE$PRESENTA

Report message to a moderator

Re: Sql help [message #7588 is a reply to message #7583]

Mon, 23 June 2003 16:52

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

Mahesh Rajendran,

The solution that you provided will remove too many spaces or $ when there is an even number of them. In your post, it removed them between THE and EXTRA and between BLANK and SPACES, instead of leaving one. I believe it is necessary to either nest replaces, replacing each double with a single, ensuring that there are sufficient nested replaces or use a recursive function. Please see the examples below.

Barbara

SQL> -- test data:
SQL> SELECT y FROM x
  2  /

Y                                                                               
--------------------------------------------------------------------------------
REMOVE$$$THE$$$$EXTRA$$$BLANK$$$$SPACES                                         
REMOVE$$$THE$$$$EXTRA$$$BLANK$$$$SPACES$$$WHICH$$$$ARE$$$$$PRESENT              

SQL> -- You will need to either
SQL> -- nest sufficient replaces:
SQL> SELECT REPLACE (REPLACE (REPLACE (y, '$$', '$'), '$$', '$'), '$$', '$')
  2  FROM   x
  3  /

REPLACE(REPLACE(REPLACE(Y,'$$','$'),'$$','$'),'$$','$')                         
--------------------------------------------------------------------------------
REMOVE$THE$EXTRA$BLANK$SPACES                                                   
REMOVE$THE$EXTRA$BLANK$SPACES$WHICH$ARE$PRESENT                                 

SQL> -- or use a recursive function:
SQL> CREATE OR REPLACE FUNCTION remove_doubles
  2    (p_string    IN VARCHAR2,
  3  	p_character IN VARCHAR2 DEFAULT ' ')
  4    RETURN	       VARCHAR2
  5  AS
  6  BEGIN
  7    IF INSTR (p_string, p_character || p_character) > 0 THEN
  8  	 RETURN remove_doubles (REPLACE (p_string,
  9  					 p_character || p_character,
 10  					 p_character),
 11  				p_character);
 12    ELSE
 13  	 RETURN p_string;
 14    END IF;
 15  END remove_doubles;
 16  /

Function created.

SQL> SHOW ERRORS
No errors.
SQL> SELECT remove_doubles (y, '$') FROM x
  2  /

REMOVE_DOUBLES(Y,'$')                                                           
--------------------------------------------------------------------------------
REMOVE$THE$EXTRA$BLANK$SPACES                                                   
REMOVE$THE$EXTRA$BLANK$SPACES$WHICH$ARE$PRESENT

Report message to a moderator