DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: function to delete extra spaces between words

  1. #1
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227
    Hi gang,
    Does anyone know of any oracle function that I can use to delete extraneous spaces between words in a string. For example, the input string 'this is a test data end here'.



    Rtrim and Ltrim will not delete between words!

    Thanks,
    Leonard905
    leonard905
    leonard905@yahoo.com

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Even I, if you give me that piece of text I wouldnt know which space do I have to delete?

    'this is a test data end here'

    which space has to be deleted?

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    I don't think it can be done simply by combining built-in SQL functions. The easiest way is to built your own PL/SQL function taht will remove redundat spaces. The following one is replacing two consecutive spaces with a single space until there is no more "double spaces" in the string.
    [code]
    create or replace function remove_redundant_spaces (p_instr in varchar2)
    return varchar2 is
    v_outstr varchar2(4000) := p_instr;
    v_length_before number := 0;
    v_length_after number := length(v_outstr);
    begin
    while v_length_before != v_length_after loop
    v_length_before := v_length_after;
    v_outstr := REPLACE(v_outstr, ' ', ' '); -- replace two blanks with single blank
    v_length_after := length(v_outstr);
    end loop;
    return v_outstr;
    end;
    /
    [code]
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by pando
    Even I, if you give me that piece of text I wouldnt know which space do I have to delete?

    'this is a test data end here'

    which space has to be deleted?
    Pando, you have to look at the HTML source (or click "quote" on the original post) to see there were many spaces between individual words.....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    if it was to replace two blanks you could use replace()

    Code:
    select dname from dept;
    
    DNAME
    --------------
    ACCOUNTING
    RESEARCH
    SALES
    OPERATIONS
    las   lala
    
    select replace(dname, '   ',' ') "removing extra space" from dept:
    
    removing extra
    --------------
    ACCOUNTING
    RESEARCH
    SALES
    OPERATIONS
    las lala
    thanx jmodic I didnt know I had to read in html

  6. #6
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227
    The entry was automatically reset by the browser, maybe. Here it is again:

    'this is a data test end here'


    Thanks,
    Leonard
    leonard905
    leonard905@yahoo.com

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by pando
    if it was to replace two blanks you could use replace()
    select replace(dname, ' ',' ') "removing extra space" from dept:
    This only works if there are exactly 2 blanks between the two words (like in your case between 'las' and 'lala'). What if there are 3 consecutive blanks between two words? Or 15? or 37? .... You have to do it in a loop.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by jmodic
    I don't think it can be done simply by combining built-in SQL functions
    And here I was waiting for this elaborate SQL statement using DECODE, CASE, REPLACE, etc...
    Jeff Hunter

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    YEa I did so because I readextra space not extra spaceS

  10. #10
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227
    Can I request more help here: Below is my table from which the data comes:


    SVRMGR> desc test
    Column Name Null? Type
    ------------------------------ -------- ----
    REMARKS VARCHAR2(500)
    SVRMGR>

    How I can I customize the function (previous thread) to be used on this table?

    Thanks,
    Leonard905
    leonard905
    leonard905@yahoo.com

Posting Permissions

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


Click Here to Expand Forum to Full Width