In MySql database, how to query on field without bothering about case of fields in Perl DBI

Question

I have below query in perl DBI -

$query = $dbh->prepare(
    "SELECT * FROM domain WHERE domainname = ? AND servicename = ?");
$query->execute( $domain, $service );

where the servicename can be case insensitive i.e if my existing service name is 'Storage' then it should fetch the result for 'storage' also.

I tried below code -

query = $dbh->prepare(
    "SELECT * FROM domain WHERE domainname = ? AND servicename = lower(?)");
$query->execute( $domain, $service );

But it is not working for servicename = 'storage'.

Any pointer where i have missed?

Answer 1

You should also use lowerfunction for the column value:

query = $dbh->prepare(
    "SELECT * FROM domain WHERE domainname = ? AND lower(servicename) = lower(?)");
$query->execute( $domain, $service );

Answer 2

You can use ilike function of Mysql it is case insensitive

query = $dbh->prepare(
    "SELECT * FROM domain WHERE domainname = ? AND servicename = ilike(?)"
);

$query->execute( $domain, $service );

ilike makes the comparison case insensitive.