Two-fer in Sqlite

Programming SQLite
1

In sql, blank is treated as NULL but is showing error for the ouput

UPDATE twofer
SET response =
    CASE
        WHEN Input IS NOT NULL THEN "One for "|| INPUT ||", one for me."
        WHEN Input IS NULL THEN "One for you, one for me."
    END;

check it.

2

If you look at the output, it suggests that you need to replace the empty string with “you” but are failing to do so. The input sets empty strings; the name is never set to NULL.

3

i’m updating the empty string to null but it was again fallied the test cases.

update twofer
set input = NULL
where length(input) = 0;
UPDATE twofer
SET response =
    CASE
        WHEN Input IS NOT NULL THEN "One for "|| INPUT ||", one for me."
        WHEN Input is NUll THEN "One for you, one for me."
    END;
4

What are the test errors?

5

Testcases

INSERT INTO tests (name, uuid,
                    input, expected)
    VALUES
        ('no name given', '1cf3e15a-a3d7-4a87-aeb3-ba1b43bc8dce',
            '', 'One for you, one for me.'),
        ('a name given', 'b4c6dbb8-b4fb-42c2-bafd-10785abe7709',
            'Alice', 'One for Alice, one for me.'),
        ('another name given', '3549048d-1a6e-4653-9a79-b0bda163e8d5',
            'Bob', 'One for Bob, one for me.');

One test is failled

"",""
"Alice",""
"Bob",""

For the (“”) empty string, I try to update it to null after I update the case statement for the output.

update twofer
set input = NULL
where length(input) = 0;
UPDATE twofer
SET response =
    CASE
        WHEN Input IS NOT NULL THEN "One for "|| INPUT ||", one for me."
        WHEN Input is NUll THEN "One for you, one for me."
    END;
6

I solved this problem by changing the logic,
where length of input is greater than one and less than one

UPDATE twofer
SET response =
    CASE
        WHEN length(Input) >1 THEN "One for "|| INPUT ||", one for me."
        WHEN length(Input) <1 THEN "One for you, one for me."
    END;
7

You could also test for the empty string. Input = ""

1 Like
8

I tried an online sqlite editor:

SELECT IIF("" IS NULL, "Empty string is null", "Empty string is not null");

The output is “Empty string is not null”.

If you change the actual value from “” to null would be correct for your logic, but the test runner will not pick it up, because the test-runner compares the unedited entry with the one that is in the database after your code has been run. As it fails to find the original entry, the test case fails.

1 Like