Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Getting length of longest value in a column

by Qukz (Novice)
on Feb 20, 2012 at 18:43 UTC ( #955125=perlquestion: print w/ replies, xml ) Need Help??
Qukz has asked for the wisdom of the Perl Monks concerning the following question:

Hello, I'm trying to run a query and then from the results get the length of the longest value in a particular column and then use that number to pad the other columns so that all columns are equal widths... I know there are other ways to do this, but I'm (obviously) new and I'd like to know how to do it without any additional modules.

I've tried quite a few things (which have all failed for one reason or another), so to save space/everyone's time I'll just include the working code of the query:

use strict; use warnings; my $sth = $dbh->prepare($query); $sth->execute(); my ($TICKET_ID,$PRIORITY,$ABSTRACT); $sth->bind_columns(\($TICKET_ID,$ABSTRACT,$PRIORITY)); while (my $row = $sth->fetchrow_arrayref) { print "$TICKET_ID $ABSTRACT $PRIORITY \n"; }

So this returns something like:

TICKET123 Server unpingable Medium TICKET234 RPDU Audit Medium TICKET345 Network down High Ticket456 Check serial number Low Ticket567 Critical server unpingable High

So I'd like to get the length of the longest abstract, then pad the others so they're all the same width, making it look more like:

TICKET123 Server unpingable Medium TICKET234 RPDU audit Medium TICKET345 Network down High Ticket456 Check serial number Low Ticket567 Critical server unpingable High

Any help is greatly appreciated.

Comment on Getting length of longest value in a column
Select or Download Code
Re: Getting length of longest value in a column
by JavaFan (Canon) on Feb 20, 2012 at 18:49 UTC
    To be able to determine the longest value, you need to get all of them. So, I'd use something like:
    ... $sth->execute; my $all = $sth->fetchall_arrayref; my @max = (0) x 3; foreach my $row (@$all) { for (my $i = 0; $i < @$row; $i++) { $max[$i] = length $$row[$i] if length $$row[$i] > $max[$i]; } }
    Now $max[0] contains the length of the largest ticket_id, $max[1] the length of the largest abstract, and $max[2] the length of the largest priority.
Re: Getting length of longest value in a column
by cursion (Monk) on Feb 20, 2012 at 19:27 UTC

    In the real world I might get lazy and make the arguement that the high/medium/low info should be right after the ticket number - because it is important. Lining that up is easy.

    In the world of doing it the way someone says to (also part of the real world I guess), check out the documentation for sprintf (perldoc -f sprintf), it'll help you pad stuff.

Re: Getting length of longest value in a column
by BrowserUk (Pope) on Feb 20, 2012 at 19:44 UTC

    Get the DB to tell you the maximum length of the field. Assuming a well-trimmed varchar field called 'reason':

    select max( len( reason ) ) from table;

    If the field is fixed length or can having space padding, then you'd need to trim them up. The functions for that tend to vary from DB to DB.


    With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday'
    Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
    "Science is about questioning the status quo. Questioning authority".
    In the absence of evidence, opinion is indistinguishable from prejudice.

    The start of some sanity?

      Actually even len/length/char_length varies from DB to DB. Of the two I've worked with (SQLite and MySQL), one returns the number of bytes for length where the other returns the number of characters. Neither provide len. MySQL provides char_length in addition to length to return the character count.

      True laziness is hard work
Re: Getting length of longest value in a column
by repellent (Priest) on Feb 21, 2012 at 04:05 UTC
    Have the database do the work for you:
    my $query = <<'SQL'; SELECT ticket_id, abstract, priority, maxlen_abstract FROM (SELECT MAX(LENGTH(abstract)) AS maxlen_abstract FROM t1) AS s1 CROSS JOIN t1; SQL

    Pay special attention to what your definition of length should be. You may not be in an ASCII-world.
Re: Getting length of longest value in a column
by choroba (Abbot) on Feb 21, 2012 at 11:14 UTC
    Besides using the database to count the length, you can also use Text::Table.
Re: Getting length of longest value in a column
by sriharsha.sm (Initiate) on Feb 21, 2012 at 13:40 UTC
    I had suggest you to use Text::ASCIITable since it provides the convenient options to add reach row.
    @array1 = ("a", "a sdfasdf value" , "medium"); @array2 = ("b", "a asdfa asdfasdfsadfs value" , "medium"); @array3 = ("c", "a value" , "high"); use Text::ASCIITable; $t = Text::ASCIITable->new(); $t->setCols('Id', 'Name' ,' Pri'); $t->addRow(@array1); $t->addRow(@array2); $t->addRow(@array3); print $t;
    Output:
    .------------------------------------------------. | Id | Name | Pri | +----+----------------------------------+--------+ | a | a sdfasdf value | medium | | b | a asdfa asdfasdfsadfs value | medium | | c | a value | high | '----+----------------------------------+--------'
Re: Getting length of longest value in a column
by Marshall (Prior) on Feb 22, 2012 at 03:20 UTC
    As a bit of practical advice, it is often not desirable to set the column widths to the "max" of the "longest text". A more common thing is like this:
    #!/usr/bin/perl -w use strict; my ($ticket,$text,$priority) = ("Ticket567", "Critical server unpingable", "High"); printf "%-9s %-30s %s", $ticket,$text,$priority; # Ticket567 Critical server unpingable High
    Use printf and a minimum field width specifier that is set to what works in the vast majority of cases. The "%-30s" means to reserve 30 spaces and left justify the text in that field. If this line's text has more than 30 characters, no data is lost (it will still print), but the columns after that one will be displaced.

    Invariably somebody is going to type in the "Gettysburg Address" instead of a simple problem abstract. If every line uses that column's huge width, then the whole report is going to look screwy. If you use fixed widths that work "almost all the time", then when this happens just one line of the printout doesn't line up nicely - rather than having huge amounts of white space in all of the other lines to accommodate this one weirdo line.

    Another thing that happens, is that often folks will want to cut and paste lines from different report days together into some new document. If the report column widths are consistent day to day, this is easier.

    It is not my place to say that your requirement is "wrong" or that you don't need it. But you asked: "Any help is greatly appreciated.", I'm just presenting a plausible, practical alternative from the "real world".

    Update: In printf "%-9s %-30s %s", this could have been written as printf "%-10s%-31s%s", but it is usually a mistake to rely upon the field width to account for the blank space between columns. By putting an explicit space in between fields in the format spec, when a column "overflows" because it is too wide to fit into the minimum space, this guarantees that there still be at least one space between the columns. If we were say printing integers and had "%3i %4i", that space ensures that we won't windup with something like "12345123456789" instead of "12345 123456789". This is a fine point, but understanding this will save you grief on some reporting project. Putting that explicit space in the format spec also makes it easier to read, so its a "win-win".

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://955125]
Approved by Corion
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (14)
As of 2014-12-19 17:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (90 votes), past polls