Re: Deleting or Trimming White Spaces - addendum

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 19 Jan 2003 18:36:06 -0800
Message-ID: <92eeeff0.0301191836.203c1d8f@posting.google.com>

Rene Nyffenegger <rene.nyffenegger_at_gmx.ch> wrote in message news:<b0e4aj$nd85p$3_at_ID-82536.news.dfncis.de>...
> Hello, I like your solution. Still, can you also give an example that replaces
> three or more consecutive spaces:
>
> create table test_ (company_ VARCHAR2(50));
>
>
> insert into test_ values ('My Own Company');
> insert into test_ values ('His Company');
> insert into test_ values (' Her Company ');
> insert into test_ values (' Bruno''s Company in Madrid');
> insert into test_ values (' Heidi''s Company in London ');
>
> commit;
>
>
> select replace(ltrim(rtrim(company_)), ' ', ' ') from test_;
>
> drop table test_;
>
> Output =>
>
> REPLACE(LTRIM(RTRIM(COMPANY_)),'','')
> -------------------------------------
> My Own Company
> His Company
> Her Company
> Bruno's Company in Madrid
> Heidi's Company in London
>
>
> ... There are still spaces in Heidi's company.
>
>
> Thanks for any pointer
>
> Rene

There are atleast 3 ways I could think of.

1. Run above sql statement multiple times until there are no more spaces > 1.
2. Run above statement in a loop inside a PLSQL block.
3. Use a function to fix the string for you.

I'll let you test options 1 and 2. You could write the function in pure PLSQL but I already had java class written out for something else and just extracted one function out of it that does the job you are looking for. I did this in Java because it has a powerfull StringTokenizer class to tokenize the entire string and not much code is required. You would need to have Java option installed in the database for this. I tested it in 9.2 but it should also work in 8.1.x.

• Java Source CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "strTokenizer" AS import java.util.StringTokenizer;

   public class strTokenizer {

      public static String removeSpaces (String str) 
         throws Exception {
         
         StringTokenizer st = new StringTokenizer(str);
         String fixstr = new String();
         while (st.hasMoreTokens()) {
            fixstr = fixstr + " " + st.nextToken();
         }
         return fixstr.trim();   
      }

   }
/

• Wrapper function CREATE OR REPLACE FUNCTION Remove_Spaces ( string_ VARCHAR2 ) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'strTokenizer.removeSpaces (java.lang.String) return java.lang.String'; /

Update test_

   Set company_ = Remove_Spaces(company_);

Regards
/Rauf Sarwar Received on Sun Jan 19 2003 - 20:36:06 CST