Puzzle #1

May 13, 2011 at 1:22pm

Given:

 

CREATE TABLE puzzle ( text_column VARCHAR2(4000) );

 

Question:

 

Assume the table has been undergone many unknown INSERTs. What is the minimum integer value that can result for minimum_column and the maximum integer value for maximum_column?

 

SELECT MIN(LENGTH(text_column)) as minimum_column

     , MAX(LENGTH(text_column)) as maximum_column 

  FROM puzzle;

 

Answer:

 

The minimum integer value is 1, the maximimum is 4000.

 

The value cannot be more than 4000 because of the column definition.  The value cannot be less than 1 because the LENGTH() built-in can never return less than 1.

 

To understand the behavior of LENGTH() you must understand that a VARCHAR2 value that is empty or has no character data is NULL and unknown rather than a known zero-length string value.  LENGTH(), like most (but not all) methods, returns NULL when evaluating NULL input.

 

Whenever possible, you should not allow NULL in your schema.  If you allow NULL, your SQL will be more complex (and slower performing) to accomodate unknown values.  For instance you could introduce NVL() to get the 0 value in your SQL.

 

Proof:

 

CREATE TABLE puzzle ( text_column VARCHAR2(4000) );

 

INSERT INTO puzzle VALUES ('x');

INSERT INTO puzzle VALUES ('xz');

 

INSERT INTO puzzle VALUES (''); -- same as null

INSERT INTO puzzle VALUES (null); -- same as ''

 

INSERT INTO puzzle VALUES (rpad('x',500,'z'));

INSERT INTO puzzle VALUES (rpad('x',4000,'z'));

INSERT INTO puzzle VALUES (rpad('x',4000,'z') || 'some extra'); -- too long

 

SELECT text_column, length(text_column) FROM puzzle;

 

SELECT text_column, nvl(length(text_column),0) FROM puzzle;

 

SELECT MIN(LENGTH(text_column)) as minimum_column

     , MAX(LENGTH(text_column)) as maximum_column 

  FROM puzzle;