DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Remove whitespaces/blanks from a string

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Hi,

    How do I remove blanhs from with a string using a select statement.

    For example the tring is 'Ron nie' and I want my select to get 'Ronnie'.

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    SELECT REPLACE('Ron nie', ' ') FROM dual;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Thanks,

    Actually I am looking for something where the whitespace is removed only after a comma and not otherwise

    Like in this case the blank should not be removed in the statement

    'Tom Hanks'

    But in this case the blank should be removed
    'Tom, Hanks'

    Notice the comma here. Hence I am trying to remove blank spaces around a comma and in all other cases it should ignore

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Code:
    SQL> SELECT REPLACE('Tom Hanks' , ', ', ',') FROM dual;
    
    REPLACE('
    ---------
    Tom Hanks
    
    SQL> SELECT REPLACE('Tom, Hanks' , ', ', ',') FROM dual;
    
    REPLACE('
    ---------
    Tom,Hanks
    
    SQL>
    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
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Thanks Jurij,

    I want to remove all the occurences of whitespaces after a comma.

    This is what i get with the above query

    1* SELECT REPLACE('Tom, Hanks' , ',&nbsp ', ',') FROM dual
    SQL> /

    REPLACE('TOM,&nbsp&nbsp&nbsp&nbspHANKS',',',',')
    ---------------------------------
    Tom,&nbsp&nbsp&nbsp&nbspHanks


    It should be

    Tom,Hanks


    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    I'm affraid you'll have to use PL/SQL for that. Something like the following function:
    Code:
    SQL> create or replace function remove_blanks_after_comma (p_in in varchar2)
      2  return varchar2 as
      3    v_out varchar2(4000) := p_in;
      4  begin
      5    while INSTR(v_out, ', ') > 0 loop
      6      v_out := replace(v_out, ', ', ',');        
      7    end loop;
      8    return v_out; 
      9  end;
     10  /
    
    Function created.
    
    SQL> SELECT remove_blanks_after_comma('Tom,   Hanks') FROM dual;
    
    REMOVE_BLANKS_AFTER_COMMA('TOM,HANKS')
    --------------------------------------------------------------------------------
    Tom,Hanks
    
    SQL> SELECT remove_blanks_after_comma('Tom    Hanks') FROM dual;
    
    REMOVE_BLANKS_AFTER_COMMA('TOMHANKS')
    --------------------------------------------------------------------------------
    Tom    Hanks
    
    SQL>
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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