You want the LIKE function.:

my $sql = 'SELECT *
            FROM clttable
            WHERE clt LIKE ?';
my $sth = $dbi->prepare $sql;
[download]

LIKE patterns are simple, _ is wild for a single character, % matches any number including none.

I set this up with a placeholder so you don't need to prepare each query.

Update Oops, mixed patterns with shell in my head, repaired.

After Compline,
Zaxo

use strict;
use DBI;

my $dbh = DBI->connect(
   'DBI:mysql:CLTE',
   {RaiseError=>1}
);

my $sql = "
   SELECT * FROM clttable
   WHERE clt LIKE ?
";

my $sth = $dbh->prepare($sql);
$sth->execute($INPUT{clt} . '%');

my $row = $sth->fetchrow_hashref;

$sth->finish;
$dbh->disconnect;
[download]

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

>>"select * is bad news"

What if you want all of them and you're not sure what the columns are?

Watch closely as I boggle at your statement. Boggle.

Why, er, how are you working with a table where you don't know what the columns are? I can imagine not knowing the column names in advance if you are writing some generic database code that is expected to work with multiple tables or something like that. I included some code form a project I'm working on that demonstrates such a generic function with some PostgreSQL specific bits added in. The only reason I'd expect jeffa to deprecate 'select * ...' is that you can easily chew up memory if you aren't careful. In my case I get back a single row so there's no problem. Message me if you'd like to see the rest of the code.

# ####################################################################
+##
# Voter::Base Object methods
# ####################################################################
+##

sub populate
  {
    # This function is normally used only by object methods. It takes
    # no arguments and returns a boolean value. It returns false
    # if the object doesn't actually exist.
    # 1 / 0 = $object->populate
    
    # Object method
    # 
    # $_[0] = Voter::...=HASH( ... )
    $_[0]->ERR_PARAMC unless
      ( @_ == 1 and
    ref( $_[0] ) );
    
    # The additional attributes tableoid, oid, xmin, cmin, xmax, cmax,
+ ctid are
    # PostgreSQL specific and exist on every row in every table. They 
+might be
    # interesting so I'm fetching them as well though any actual use o
+f them
    # should take care to use the values correctly upon consultation w
+ith
    # the PostgreSQL documentation.
    my $identity = $_[0]->identity_sql('SELECT');
    
    # Don't just execute this inline - I want to keep the $sth handle 
+around
    my $sth = $_[0]->dbh->prepare( "SELECT tableoid, oid, xmin, cmin, 
+xmax, cmax, ctid, * FROM " .
                   $_[0]->CLASS .
                   " WHERE " .
                   $identity->{exp} );
    $sth->execute( $identity->{val} );
    my $ary = $sth->fetchrow_arrayref;
    
    # Return nothing if the object doesn't exist
    return 0 unless $ary;
    
    # Get the list of attribute names and assign their values as a has
+h slice
    @{$_[0]}{ @{$sth->{NAME_lc}} } = @$ary;
    # that's just assigning an array to a hash slice - except all thro
+ugh
    # references. More prosaicly it might look like
    # @_{ @attributes } = @values
    
    return 1;
  }
[download]

__SIG__
printf "You are here %08x\n", unpack "L!", unpack "P4", pack "L!", B::svref_2object(sub{})->OUTSIDE

I just try to avoid select * in any serious 'production' type code that i might churn out. Sure, i have been known to be lazy and use select * (i've also been know to use dot star in my regexes!), but i (hopefully) will turn right around and replace the * with the column names. Now, consider this silly but possible example:

Say you have two tables, both with a field named 'id' and a field named 'title':

my $sth = $dbh->prepare('
   select * from album,song
   limit 2
');

$sth->execute();
print Dumper $sth->fetchrow_hashref();

print Dumper $sth->fetchrow_arrayref();

__END__
$VAR1 = {
    'album_id' => '1',
    'title' => 'Until the End of the World',
    'artist_id' => '1',
    'year' => '1991',
    'id' => '1'
  };
$VAR1 = [
    '2',
    'War',
    '1983',
    '1',
    '1',
    'Until the End of the World',
    '1'
  ];
[download]

See the difference? By avoiding select * (or just being happy with an array or array ref) and explicitly specifying the columns, you leave open the option to rename those columns, allowing you to safely store the results in a hash without worry of clobbering existing keys.

So, how do you select the columns when you don't know ahead of time what they will be. Most likely in a vendor-specific way. Here is one way to do it with MySQL:

my @fields = map {
   $_->[0]
} @{$dbh->selectall_arrayref('desc song')};

my $sth = $dbh->prepare("
   select @{[join(',',@fields)]}
   from song
");
$sth->execute();
[download]

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

Hello:

Well, I wanted to include 50 columns, Didn't know select * would hurt it. I guess I should just type all 50 column names out? i.e select id, clte, claimno, date, name, city, etc.

Anthony

Implicit in Zaxo and Jeffa's replies is that you should use placeholders because your interpolation opens you up to SQL insertion attacks. I'm sure someone here has a good node on why and how that can go wrong. It boils down to checking your user input before giving it to your database. Your code as written allows your web user to execute arbitrary SQL code which is a Bad Thing.

__SIG__
printf "You are here %08x\n", unpack "L!", unpack "P4", pack "L!", B::svref_2object(sub{})->OUTSIDE