Home » SQL & PL/SQL » SQL & PL/SQL » padding ";" in the table result
padding ";" in the table result [message #246898] Fri, 22 June 2007 07:11 Go to next message
NIckman
Messages: 64
Registered: May 2007
Member
Hi
I have oracle emp table

col def of that table are
name varchar 200
status varchar 50
date date
code number(20)

when i run select query,
i got result in name column with actaul name + rest of them spaces.
i am getting spaces rest of unfilled column length.
say for example actual name is 10 char length
rest of the 190 chars are spaces of name column.
what i want is
after the actual name, i need padd with ";"
i do not want spaces.
i tried this
it is not working.


select rpad(rtrim(name,';'),
rpad(rtrim(status,';'),
rpad(rtrim(date,';'),
rpad(rtrim(code,';')
from emptab
order by name;

i aso tried this


select rpad(rtrim(name,';')||','||
rpad(rtrim(status,';')||','||
rpad(rtrim(date,';')||','||
rpad(rtrim(code,';')||','||
from emptab
order by name;

i need output like this

aaaaa;bbbbbbb;cccc;dd
zzz;mmmm;nnnnnn;ww
pl hlep me
thx
N.

Re: padding ";" in the table result [message #246902 is a reply to message #246898] Fri, 22 June 2007 07:29 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Well, for a start rpad requires 3 parameters, you are missing the 2nd parameter which should be the length that you want to pad to.
If your tables truly ARE varchars then this statement
Quote:
say for example actual name is 10 char length
rest of the 190 chars are spaces of name column.
is wrong. If they are varchars containing 10 characters then their length is 10 and there is no padding. Are you sure that they are not Char datatype? Also, you are missing closing parentheses from each of the function calls.
Rather than type in what you say you have done, perform the tasks in sqlplus and copy and paste the session
Finally, you have 28 posts to your name yet you still seem to be unwilling or unable to format your posts correctly. Have a look at the sticky at the top of the forum list to see how to format your posts.

[Updated on: Fri, 22 June 2007 07:30]

Report message to a moderator

Re: padding ";" in the table result [message #246903 is a reply to message #246898] Fri, 22 June 2007 07:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You know how to format your post.
Do it!

Regards
Michel
Re: padding ";" in the table result [message #247003 is a reply to message #246898] Fri, 22 June 2007 15:36 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
select rtrim(name)||';'||
       rtrim(status)||';'||
       rtrim(date)||';'||
       rtrim(code)
from emptab
order by name;
Re: padding ";" in the table result [message #247015 is a reply to message #247003] Fri, 22 June 2007 17:40 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
Thanks for your reply. I really appreciate.
it works.
N.
Re: padding ";" in the table result [message #247030 is a reply to message #247015] Sat, 23 June 2007 00:08 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And we'd really appreciate if you format your post.

Regards
Michel
Previous Topic: find column name in database
Next Topic: UPTIME Record?
Goto Forum:
  


Current Time: Fri Dec 09 01:57:17 CST 2016

Total time taken to generate the page: 0.16204 seconds