Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hello

I have a database that makes - unfortunately - a bit of mix of undef and empty values. In the following script, I would like to match with $Tag1 and $Tag2 being "" (empty) if the corresponding values in the database is empty "" OR undef. Is this possible?

my $sql = 'SELECT * FROM GlossaryMetadata WHERE Tag1 LIKE ? AND Tag2 L
+IKE ?';
$sth = $dbh->prepare($sql);
$sth->execute($Tag1, $Tag2);

[download]

Replies are listed 'Best First'.
Re: DBI match both empty and undef
by tobyink (Canon) on Jan 27, 2019 at 17:03 UTC

    Hopefully the Tag1 is null or Tag1 = '' solution will work for you, but I'd like to point out that your database design appears to violate first normal form. I'm a pragmatist and realise there's often reasons for violating things that are held to be good design principles, but first normal form is a pretty useful guideline for database design. The Wikipedia article I linked gives a pretty good summary for what first normal form is, why it's a good idea, and how to make your database follow it.

    toby döt ink
[reply]
[d/l]
Re: DBI match both empty and undef
by pme (Monsignor) on Jan 27, 2019 at 15:21 UTC
    Hi,

    What kind of database you have? For example in Oracle database the empty string becomes null automagically. This is how to select rows where both Tag1 and Tag2 is null. null value in the database is seen as undef in perl. I suppose you use DBI module. 

    SELECT * FROM GlossaryMetadata WHERE Tag1 is null AND Tag2 is null;

    [download]
[reply]
[d/l]

      SQLite and MSSQL. Both make a difference between undef and empty, and I would like to match both.

[reply]
        SELECT * FROM GlossaryMetadata WHERE (Tag1 is null or Tag1 = '') AND (
+Tag2 is null or Tag2 = '');

        [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom