Home » SQL & PL/SQL » SQL & PL/SQL » Trim extra space (merged)
Trim extra space (merged) [message #223062] Wed, 07 March 2007 07:36 Go to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Hi.
Please help me to trim extra white space in my output.

Below is my program to concatenate the following fields
.predirection
.prefix
.streetname
.suffix
.sufdirection
.extension
and also compress out excess blanks of the fields. But seperate each part with a blank then count the character.

However,the out put give me an extra space if one of the field has no data (please see the output at the end)

Please help me to get rid of the extra white space. I would appreciate that.

***************************************************

DECLARE
cursor c1 is select * from address;

streetname varchar2(60);
count_char number(2);
BEGIN
FOR v_rec in c1 LOOP
Streetname := trim(both from v_rec.predirection)||' '||trim(both from v_rec.prefix)||' '||trim(both from v_rec.name)||' '||trim(both from v_rec.suffix)||' '||trim(both from v_rec.sufdirection)||' '||trim(both from v_rec.extension);
count_char := 0;
for k in 1 .. 60 loop
if substr(streetname,k,1) != ' ' then
streetname := streetname + 1;
end if;
end loop;
dbms_output.put_line(streetname||'Count: '||count_char);
END LOOP;
END;
/

********************************
Output:


EAST(2 space here)MARTIN LUTHER KING JR DRIVE (extra space between EAST and MARTIN because the PREFIX column has no data)
BANDERA ROAD(2 space here)EXT (extra space between ROAD and EXT because
the SUFFIX column has no data)
Urgent. Please help. [message #223330 is a reply to message #223062] Thu, 08 March 2007 08:08 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Hi.
Please help me to trim extra white space in my output.

Below is my program to concatenate the following fields
.predirection
.prefix
.streetname
.suffix
.sufdirection
.extension
and also compress out excess blanks of the fields. But seperate each part with a blank then count the character.

However,the out put give me an extra space if one of the field has no data (please see the output at the end)

Please help me to get rid of the extra white space. I would appreciate that.

***************************************************

DECLARE
cursor c1 is select * from address;

streetname varchar2(60);
count_char number(2);
BEGIN
FOR v_rec in c1 LOOP
Streetname := trim(both from v_rec.predirection)||' '||trim(both from v_rec.prefix)||' '||trim(both from v_rec.name)||' '||trim(both from v_rec.suffix)||' '||trim(both from v_rec.sufdirection)||' '||trim(both from v_rec.extension);
count_char := 0;
for k in 1 .. 60 loop
if substr(streetname,k,1) != ' ' then
streetname := streetname + 1;
end if;
end loop;
dbms_output.put_line(streetname||'Count: '||count_char);
END LOOP;
END;
/

********************************
Please help me to trim the extra white space between EAST and MARTIM LUTHER KING JR DRIVE. I need only one space here but the output gave me 2 spaces. Pleas help.
Thanks much.

Streetname Output:
EAST(there is 2 space here)MARTIN LUTHER KING JR DRIVE
(extra space between EAST and MARTIN because the PREFIX column has no data)

Re: Urgent. Please help. [message #223332 is a reply to message #223330] Thu, 08 March 2007 08:14 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
If it's so urgent, you could have thought of a better subject than "Urgent. Please help."

Merged.
Re: Urgent. Please help. [message #223334 is a reply to message #223332] Thu, 08 March 2007 08:19 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
I am a new one in this forum. This is my first time I posted my question. I don't know how to put in my subject. But It is really urgent to me. Then I put it in the subject. I appologize for that.
Re: Urgent. Please help. [message #223337 is a reply to message #223334] Thu, 08 March 2007 08:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yuo need NVL2:
SQL> select nvl2(col_1,col_1||' ',null) || 
  2         nvl2(col_2,col_2||' ',null) || 
  3         nvl2(col_3,col_3||' ',null) || 
  4         nvl2(col_4,col_4||' ',null)
  5  from 
  6  (select 'Text'          col_1
  7        ,'more text'      col_2
  8        ,null             col_3
  9        ,'even more text' col_4
 10   from dual);

NVL2(COL_1,COL_1||'',NULL)||NV
------------------------------
Text more text even more text
Re: Urgent. Please help. [message #223339 is a reply to message #223337] Thu, 08 March 2007 08:51 Go to previous message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Thanks much for your reply.
Previous Topic: Combine 2 columns
Next Topic: To Calculate Prime Numbers in a range between 1 and 100 and insert it into a table
Goto Forum:
  


Current Time: Sat Dec 03 22:17:05 CST 2016

Total time taken to generate the page: 0.07891 seconds