trim spaces between words of a string

skd · #1 (**permalink**) 09-04-03, 10:30

I want to trim spaces between two or more words of a string by single space. for eg:- input string is 'HIGHLAND RD' and output string should be 'HIGHLAND RD'.
i was using REPLACE but it doesn' work as i thought first
v_streetNameAndType :=
REPLACE(LTRIM(p_streetNameAndType),' ',' ');

wondering is there any alternate way to do this.

i am newbie to oracle and haven't used any function.

if i need a function for above , is this close
======================================
FUNCTION SqueezeToOneSpace ( p_instr IN VARCHAR2,
p_outstr OUT VARCHAR2) RETURN VARCHAR2 IS

v_instr VARCHAR2(64);
v_tmpstr VARHCAR2(64);

BEGIN

v_instr:=p_instr;
FOR ' ' IN v_inStr LOOP

v_tmpstr=REPLACE(v_instr,' ',' ');

END LOOP;

p_outstr:=v_tmpstr;

END;
======================
Please help

andrewst · #2 (**permalink**) 09-04-03, 12:58

Quote:

Originally posted by skd
I want to trim spaces between two or more words of a string by single space. for eg:- input string is 'HIGHLAND RD' and output string should be 'HIGHLAND RD'.
i was using REPLACE but it doesn' work as i thought first
v_streetNameAndType :=
REPLACE(LTRIM(p_streetNameAndType),' ',' ');

wondering is there any alternate way to do this.

i am newbie to oracle and haven't used any function.

if i need a function for above , is this close
======================================
FUNCTION SqueezeToOneSpace ( p_instr IN VARCHAR2,
p_outstr OUT VARCHAR2) RETURN VARCHAR2 IS

v_instr VARCHAR2(64);
v_tmpstr VARHCAR2(64);

BEGIN

v_instr:=p_instr;
FOR ' ' IN v_inStr LOOP

v_tmpstr=REPLACE(v_instr,' ',' ');

END LOOP;

p_outstr:=v_tmpstr;

END;
======================
Please help

Try this:

Code:

CREATE OR REPLACE
FUNCTION SqueezeToOneSpace
  ( p_instr IN VARCHAR2
  ) RETURN VARCHAR2
IS
  v_outstr VARCHAR2(32767) := p_instr;
BEGIN
  WHILE v_outstr LIKE '%  %' LOOP
    v_outstr := REPLACE( v_outstr, '  ', ' ' );
  END LOOP;
  RETURN v_outstr;
END;
/

skd · #3 (**permalink**) 09-04-03, 13:53

Quote:

Originally posted by andrewst
Try this:

Code:

CREATE OR REPLACE
FUNCTION SqueezeToOneSpace
  ( p_instr IN VARCHAR2
  ) RETURN VARCHAR2
IS
  v_outstr VARCHAR2(32767) := p_instr;
BEGIN
  WHILE v_outstr LIKE '%  %' LOOP
    v_outstr := REPLACE( v_outstr, '  ', ' ' );
  END LOOP;
  RETURN v_outstr;
END;
/

Thanks for quick help. wondering if this can handle tab between words?