Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    71

    Unanswered: trim spaces between words of a string

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: trim spaces between words of a string

    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;
    /

  3. #3
    Join Date
    Sep 2003
    Posts
    71

    Thumbs up Re: trim spaces between words of a string

    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?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: trim spaces between words of a string

    Originally posted by skd
    Thanks for quick help. wondering if this can handle tab between words?
    Tab is CHR(9), so just TRANSLATE( string, CHR(9), ' ' ) to turn tabs to spaces before doing the rest.

  5. #5
    Join Date
    Sep 2003
    Posts
    71

    Cool Re: trim spaces between words of a string

    Originally posted by andrewst
    Tab is CHR(9), so just TRANSLATE( string, CHR(9), ' ' ) to turn tabs to spaces before doing the rest.
    THANKS VERY MUCH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •