|
|
|
Re: Removing empty space [message #604317 is a reply to message #604307] |
Sat, 28 December 2013 05:32 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
If you mean leading/trailing spaces and new lines, use rtrim + ltrim:
SQL> DECLARE
2 l_str varchar2(32767):='
3
4
5 ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column
6 Oracle 12.1.0.1.0 Enterprise Edition 64bit on Windows 7 Professional
7
8 ';
9 p_status varchar2(100);
10 BEGIN
11 dbms_output.put_line('[' || rtrim(ltrim(l_str,chr(10) || ' '),chr(10) || ' ') || ']');
12 END;
13 /
[ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column
Oracle 12.1.0.1.0 Enterprise Edition 64bit on Windows 7
Professional]
PL/SQL procedure successfully completed.
SQL>
SY.
|
|
|
|
Re: Removing empty space [message #604323 is a reply to message #604321] |
Sat, 28 December 2013 09:59 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
You could also use regular expressions, although not sure about performance:
SQL> DECLARE
2 l_str varchar2(32767):='
3 ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column
4 Oracle 12.1.0.1.0 Enterprise Edition 64bit on Windows 7 Professional
5 ';
6 p_status varchar2(100);
7 BEGIN
8 dbms_output.put_line('[' || regexp_replace(l_str,'^(\s)+(.*?)(\s)+$','\2',1,1,'n') || ']');
9 END;
10 /
[ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column
Oracle 12.1.0.1.0 Enterprise Edition 64bit on Windows 7
Professional]
PL/SQL procedure successfully completed.
SQL>
SY.
|
|
|
Re: Removing empty space [message #604577 is a reply to message #604323] |
Wed, 01 January 2014 10:30 |
shahzad-ul-hasan
Messages: 615 Registered: August 2002
|
Senior Member |
|
|
SQL> with t as (
2 select 'AAA BBB CCC' col from dual union
3 select 'DDDD EEE F' col from dual
4 )
5 --
6 -- actual query:
7 --
8 select regexp_replace(col, '[[:space:]]+', chr(32)) col
9 from t;
COL
-----------------------------------------------------------------
AAA BBB CCC
DDDD EEE F
Another:
ed
Wrote file afiedt.buf
1 create or replace function crunch (ins varchar2) return varchar2
2 as
3 ous varchar2(4000);
4 tmp varchar2(4000);
5 begin
6 tmp:= ins;
7 while (instr(tmp,' ',1) > 0 )
8 loop
9 ous := ous || ' ' || substr(tmp,1,instr(tmp,' ',1)-1);
10 tmp := trim(both ' ' from substr(tmp,instr(tmp,' ',1)));
11 end loop;
12 ous := ous || ' ' || trim(tmp);
13 return trim(ous);
14* end crunch;
@ORCL>/
Function created.
@ORCL>select crunch('AAA BBB CCC') cstr from dual;
CSTR
--------------------------------------------------------------------------
AAA BBB CCC
@ORCL>
|
|
|