Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

database sorter script

by santander (Acolyte)
on Dec 06, 2004 at 11:55 UTC ( #412626=perlquestion: print w/replies, xml ) Need Help??
santander has asked for the wisdom of the Perl Monks concerning the following question:

Does someone could recommend me convenient database sorter script? I need which will select the specif part of database and display it when I choose the specific letter.
[A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [Q] [R] [S] [T] [U] [V] [W] [X] [Y] [Z]

Edited by davido: Wrapped square-bracketed characters in code tags.

Replies are listed 'Best First'.
Re: database sorter script
by Luca Benini (Scribe) on Dec 06, 2004 at 12:18 UTC
    What database? Your database support cursors or not?
    Really basic way can be:
    SELECT * FROM people WHERE Name LIKE 'A%' ORDER BY name;
    The A in 'A%' will be the letter are you interested to.
      Like is evil. They do full table scans since a db can't do a function index against it. If there are any databases thatcan do function indexes against "like", I'd love to know.

      You the OP would be better off, if he were using a formal DB, to create a column called indexed letter or something, that contains only the first letter, and select off of that.

      Give me strength for today.. I will not talk it away..
      Just for a moment.. It will burn through the clouds.. and shine down on me.

        Like is evil. They do full table scans since a db can't do a function index against it.If there are any databases that can do function indexes against "like", I'd love to know.
        It depends on the database, and your query. If the query begins with a constant string (e.g. where column like 'ABC%') then the database can use an index on the column. If it begins with a wildcard (e.g. where column like '%ABC') then an index on the column can not be used. If your database determines the query plan at 'prepare' time, then it will probably not use an index on a query with placeholders such as 'where column like ?' since it doesn't know what kind of arguments you'll feed it at 'execute' time.
      Sorry, its flat text database, books catalog, sort by authors data: Last Name, First Name.
        Then you'll have to go through the records, using grep if you want to filter the whole list at once, or using if if going through them one at a time.

        Assuming you have a hash per record:

        • One at a time:
          my @section; while(my $r = $db->fetchrow) { push @section, $r if $r->{name} =~ /^A/i; }
        • All at once:
          my @section = grep { $_->{name} =~ /^A/i } $db->fetchrows;
        This is pseudocode, you must fill in the details on how to get the data from the flatfile, yourself.

        Assuming it is relatively small and unsorted by default:

        #!/usr/bin/perl -w my $db = '/some/file'; my $letter = 'Y'; open DB, $db or die "Can't read $db: $!\n"; my @matches; while(<DB>){ push @matches, $_ if m/^$letter/i; } close DB; print sort @matches;

        Or more succinctly on *nix:

        $ grep -i -e ^Y db.file | sort



        There are at least a few free *real* databases around - one being PostgreSQL. I'm not sure what MySQL's current license situation is, but it might be free as well.

        My advice is to convert your flat text file database into a real database so that you can use "LIKE" in your SELECT, something like this (as someone else already pointed out):

        SELECT * FROM your_table WHERE author_last_name LIKE 'A%'
        Actually, if your flat file database is CSV (Comma Separated Value) or similar, you might be able to use Perl's DBI and DBD::CSV modules to do what you want, and you might be able to use a SELECT similar to the one above that uses the "LIKE", but I'm just speculating ;-)


        A way to do this would be like this:
        my $letter = "B"; open(my $in, "input_file") or die "Couldn't open file for read: $!"; while(<$in>) { print if m/^$letter/; }
        You could improve it slightly by stopping once you're done processing a certain letter, but I leave that as an exercise to the reader...:)


        Feel the white light, the light within
        Be your own disciple, fan the sparks of will
        For all of us waiting, your kingdom will come

        If it's a flatfile database, then either convert it to SQLite or Pg or use DBD::CSV or DBD::AnyData directly on the flatfile. With those two DBDS you can use "SELECT $cols FROM $table WHERE $field LIKE 'A%' ORDER BY $field" on a flatfile. If you don't care about the case of the letter use UPPER($field), otherwise it will find only "Ant", not "ant".

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://412626]
Approved by thor
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (4)
As of 2017-02-22 23:33 GMT
Find Nodes?
    Voting Booth?
    Before electricity was invented, what was the Electric Eel called?

    Results (338 votes). Check out past polls.