Home » SQL & PL/SQL » SQL & PL/SQL » Removing empty space (Oracle 10g)
Removing empty space [message #604307] Sat, 28 December 2013 04:05 Go to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

Hi All,

How can I remove empty space from the string.

DECLARE
l_str varchar2(32767):='                     


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 
                           
         ';
p_status varchar2(100);
BEGIN
    dbms_output.put_line(trim(l_str));
END;


Regards,
Nathan
Re: Removing empty space [message #604312 is a reply to message #604307] Sat, 28 December 2013 04:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What do you mean by "empty space"? Carriage return / new line characters?

Re: Removing empty space [message #604314 is a reply to message #604312] Sat, 28 December 2013 05:11 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

Yes sir.
Re: Removing empty space [message #604317 is a reply to message #604307] Sat, 28 December 2013 05:32 Go to previous messageGo to next message
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 #604321 is a reply to message #604317] Sat, 28 December 2013 09:34 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

I have searched many forums but unable to find the exact solution. But here I got it brilliantly.

Thank you very much to All and Happy New Year 2014.

Regards,
Nathan
Re: Removing empty space [message #604323 is a reply to message #604321] Sat, 28 December 2013 09:59 Go to previous messageGo to next message
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 Go to previous message
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>
Previous Topic: FLASHBACK_TRANSACTION_QUERY.UNDO_SQL column
Next Topic: Create DB Trigger
Goto Forum:
  


Current Time: Thu Mar 28 17:01:43 CDT 2024