Matching on empty strings, not nulls, in a MySQL column

Question

I can't get the right syntax - I'm trying to match on the column as an empty string, not a null. I've tried delimiting the string all number of ways, using single double quotes.

containerRefNo = "\"\"";

ps = getConnection().prepareStatement(
                "delete from  inumber_join where container_no = ?");

The error I receive is

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from inumber_join where container_no = '""''

When using a parameterized query you don't need to include any delimiters in the parameter value. Simply using ps.setString(1, ""); worked fine for me. — Gord Thompson
– Gord Thompson, Commented Oct 1, 2015 at 16:33
Also even if it was needed to pass the quotes, it SHOULD be the single quotes not double so: containerRefNo = "''"; this is just for clarification. @GordThompson is totally right. — Jorge Campos
– Jorge Campos, Commented Oct 1, 2015 at 16:39

Gord Thompson · Accepted Answer · 2015-10-01 17:00:54Z

1

When using a parameterized query you don't need to include any delimiters in the parameter value. Simply using ps.setString(1, ""); works fine for me.

That is, ...

// setup
try (Statement st = conn.createStatement()) {
    st.executeUpdate(
            "CREATE TEMPORARY TABLE inumber_join_temp (" +
                "id INT AUTO_INCREMENT, " +
                "container_no VARCHAR(10) NULL, " +
                "PRIMARY KEY (id)" +
            ")");
    st.executeUpdate(
            "INSERT INTO inumber_join_temp (container_no) " +
                "VALUES (null), (''), (null), (''), (null)");
}

// test
String sql = 
        "SELECT COUNT(*) AS n " +
        "FROM inumber_join_temp " +
        "WHERE container_no = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, "");  // search for empty string
    try (ResultSet rs = ps.executeQuery()) {
        rs.next();
        System.out.println(rs.getInt(1));
    }
}

... returns

answered Oct 1, 2015 at 17:00

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wax_lyrical Over a year ago

You are quite right, so let me mark this as correct. In my case, it turned out the problem was elsewhere in my query. I made a simple example for this post, and the error I received led me on a wild goose chase. In reality, I never encoded the string, it was sent from a client GUI as just an empty string. After correcting my query it all worked fine. But - sincerely - thank you for posting. It will, I hope, help others too!

Collectives™ on Stack Overflow

Matching on empty strings, not nulls, in a MySQL column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related