Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> count max number of characters
Nicolai Jorgense...
post Mar 10 2006, 02:38 AM
Post #1


Member
**

Group: Members
Posts: 13
Joined: 10-March 06
Member No.: 3,837



Hi.

I need a little help on a problem. ??

I'm trying to count the max number of characters i a column in a table.

How do i do that? It would be great if I could get at sort of a display column next to the column in mind.

E.g.

column 1 Display 1
---------- ----------
Beskrivelse #_of_char

bla bla bla 11
Go to the top of the page
 
+Quote Post
Manfred
post Mar 10 2006, 03:41 AM
Post #2


Advanced Member
***

Group: Members
Posts: 119
Joined: 23-March 05
From: Switzerland
Member No.: 1,696



If you need just the length of a particular column:

select col,length(col)
from table
Go to the top of the page
 
+Quote Post
Nicolai Jorgense...
post Mar 10 2006, 03:52 AM
Post #3


Member
**

Group: Members
Posts: 13
Joined: 10-March 06
Member No.: 3,837



QUOTE (Manfred @ Mar 10 2006, 09:42 AM) *
If you need just the length of a particular column:

select col,length(col)
from table


I a bit rusty on this one, so please be so kind as to cut it out in card-board.

I've got a column called "beskrivelse" in a table called oa_arkivkoder containing a bit more than 1000 rows. I'd like to be able to write the number of characters in each row to a new column (maybe through a view?).

Nicolai

Update.

I got it working!!!

Sometimes it is good spelling things wright in the frits, srift.trsaif (first) place.

Thanks alot for your time and help.

Nicolai
Go to the top of the page
 
+Quote Post
Manfred
post Mar 10 2006, 04:08 AM
Post #4


Advanced Member
***

Group: Members
Posts: 119
Joined: 23-March 05
From: Switzerland
Member No.: 1,696



???

select beskrivelse ,
length(beskrivelse) as #chars
from oa_arkivkoder

???

or do you like this format:

beskrivelse #chars1,#chars2,....

If I'm stilll wrong please provide an example with more than 1 row

huh.gif M
Go to the top of the page
 
+Quote Post
Nicolai Jorgense...
post Mar 10 2006, 05:56 AM
Post #5


Member
**

Group: Members
Posts: 13
Joined: 10-March 06
Member No.: 3,837



QUOTE (Manfred @ Mar 10 2006, 10:09 AM) *
???

select beskrivelse ,
length(beskrivelse) as #chars
from oa_arkivkoder

???

or do you like this format:

beskrivelse #chars1,#chars2,....

If I'm stilll wrong please provide an example with more than 1 row

huh.gif M



What if want to do for every column?

I tried

select *,length(*)
from table

No luck
Go to the top of the page
 
+Quote Post
dave
post Mar 10 2006, 06:59 AM
Post #6


Advanced Member
***

Group: Members
Posts: 5,206
Joined: 8-October 04
Member No.: 785



you will have to supply the columns you want
Go to the top of the page
 
+Quote Post
Nicolai Jorgense...
post Mar 10 2006, 07:32 AM
Post #7


Member
**

Group: Members
Posts: 13
Joined: 10-March 06
Member No.: 3,837



QUOTE (dave @ Mar 10 2006, 01:00 PM) *
you will have to supply the columns you want


Is there no way of selecting all of the columns in one go?
Go to the top of the page
 
+Quote Post
dave
post Mar 10 2006, 08:20 AM
Post #8


Advanced Member
***

Group: Members
Posts: 5,206
Joined: 8-October 04
Member No.: 785



not the way you want to do things no
Go to the top of the page
 
+Quote Post
Nicolai Jorgense...
post Mar 10 2006, 08:35 AM
Post #9


Member
**

Group: Members
Posts: 13
Joined: 10-March 06
Member No.: 3,837



QUOTE (dave @ Mar 10 2006, 02:21 PM) *
not the way you want to do things no


Ok, I see.

Then how do I do it the rigth way?

Nic
Go to the top of the page
 
+Quote Post
Manfred
post Mar 10 2006, 08:57 AM
Post #10


Advanced Member
***

Group: Members
Posts: 119
Joined: 23-March 05
From: Switzerland
Member No.: 1,696



Hi

do you just need the max(char#) of each column or all char#? Does it has to be dynamic because you like to use it for serveral tables or does the number of columns in this table changes very often?
Either you have to type the sql completly like select col1,lenght(col1),col2,lenght(col2),....colx,lenght(colx) from table or you write a procedure where you generate this SQL out of the oracle metadata, run it in the procedure and return the values. If you just need the max char# of each column like

colunm maxchar#
-------- -----------
col1 12
col2 29
....

you have to write a procedure too.

rolleyes.gif
Go to the top of the page
 
+Quote Post
Nicolai Jorgense...
post Mar 10 2006, 11:21 AM
Post #11


Member
**

Group: Members
Posts: 13
Joined: 10-March 06
Member No.: 3,837



QUOTE (Manfred @ Mar 10 2006, 02:58 PM) *
Hi

do you just need the max(char#) of each column or all char#? Does it has to be dynamic because you like to use it for serveral tables or does the number of columns in this table changes very often?
Either you have to type the sql completly like select col1,lenght(col1),col2,lenght(col2),....colx,lenght(colx) from table or you write a procedure where you generate this SQL out of the oracle metadata, run it in the procedure and return the values. If you just need the max char# of each column like

colunm maxchar#
-------- -----------
col1 12
col2 29
....

you have to write a procedure too.

rolleyes.gif


Ok. This gives me a clue. I want the max #char. The procedure should read the column headers and write the max #char. Is there a good place to find info on how to do that?

Nic
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 20th September 2014 - 11:02 PM