Re: Help: Blank spaces

From: Jurij Modic <jmodic_at_src.si>
Date: Sun, 31 Jan 1999 21:55:23 GMT
Message-ID: <36b4d0f1.4872516_at_news.arnes.si>


On Sun, 31 Jan 1999 12:14:17 GMT, Jaime F. Bermudez <jfbermudez_at_usa.net> wrote:

>Is there a way to remove extra blank spaces in a string? For example, I would
>like to convert "This is a String" into "This is a string".

You could use nested REPLACE functions if you know the maximum possible number of conseccutive extra blanks to remove from your string. For example, the following select would convert up to 10 consecutive blanks into single blank:

SCOTT_at_PO73> SELECT REPLACE(

  2           REPLACE(
  3             REPLACE('this      is     a    string',
  4             '    ', ' '), -- 4 blanks into 1
  5           '   ', ' '),    -- 3 blanks into 1
  6         '  ', ' ')        -- 2 blanks into 1
  7 FROM DUAL; REPLACE(REPLACE(

this is a string

If you want a general solution, you must create your own function, something like the following, which will replace any number of consecutive blanks into single blank:

SCOTT_at_PO73> CREATE OR REPLACE FUNCTION blank_trim   2 (p_string IN VARCHAR2) RETURN VARCHAR2   3 AS
  4 v_string VARCHAR2(2000) := p_string;   5 BEGIN
  6 WHILE INSTR(v_string,' ') > 0 LOOP   7 v_string := REPLACE(v_string,' ',' ');   8 END LOOP;
  9 RETURN v_string;
 10 END blank_trim;
 11 /

Function created.

SCOTT_at_PO73> SELECT blank_trim('this is a string')   2 FROM dual;

BLANK_TRIM('THISISASTRING')



this is a string

>Thank you,
>
>Jaime F. Bermudez

HTH, Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)



The above opinions are mine and do not represent any official standpoints of my employer Received on Sun Jan 31 1999 - 22:55:23 CET

Original text of this message