Re: Remove whitespaces in varchar2

From: Markus Eltschinger <fake_address_at_home.com>
Date: Thu, 31 Jul 2003 14:32:56 +0200
Message-ID: <1059654781.694563@exnews>

Marc,

ASCII 32 is a whitespace and ASCII 9 is a tab character. See the following SQL*Plus example on how to remove all blanks and tab characters from a character string:

set termout on
set serveroutput on size 10240
variable vc_test varchar2(20)
begin
  :vc_test := 'this' || chr(32) || chr(32) || 'is' || chr(9) || 'a' || chr(32) || 'test';
  dbms_output.put_line('BEFORE: ' || :vc_test);

  select replace(replace(:vc_test, chr(32), NULL), chr(9), NULL)     into :vc_test
    from dual;
  dbms_output.put_line('AFTER : ' || :vc_test); end;
/

Kind regards,
Markus Eltschinger

---

Swisscom IT Services Ltd
Data Warehouse Development
1752 Villars-Sur-Glāne FR
Switzerland
http://www.swisscom.com/it/content/e-BusinessSolutions/crmdwh7/bidwh/index_EN.html

"Marc Eggenberger" <marc.eggenberger_at_itc.alstom.com> wrote in message news:MPG.1992f0d5e63e7df89896bf_at_iww.cacti.ch.alstom.com...
> Hi there.
>
> I have several varchar2 columns which are updated from userinput in
> another db system. The user tend to enter the input in various ways.
> Some with spaces, some without, some with tabs etc etc ....
>
> I want to remove them before updating my main table with the data. When
> I do a replace(string1 IN VARCHAR2, pattern IN VARCHAR2) how can I
> specify whitespaces? Is there a special character as a replacement for
> Whitespaces like for example \w or similar or do I have to replace
> space, tab etc one by one?
>
> System is 8.1.7.0.0 on Windows.
>
> thanks for any hints.
>
> --
> mfg
> Marc Eggenberger
Received on Thu Jul 31 2003 - 07:32:56 CDT