Replacing an Empty String

[Deleted User]

Jim Knicely authored this tip.

In Vertica, an empty string is not treated as a NULL value, so an empty string does equal an empty string.

Example:

dbadmin=> SELECT '''' = '''' "Are they equal?";
Are they equal?
-----------------
t
(1 row)

The function REPLACE replaces all occurrences of characters in a string with another set of characters. The REPLACE function appears to work well with an empty string in a simple SELECT statement.

Example:

dbadmin=> SELECT replace('''', '''', 'Replaced!');
  replace
-----------
Replaced!
(1 row)

However, the REPLACE won’t work on empty string values in table columns having a defined data length (i.e., > 0). You need to use a CASE or DECODE statement to replace these those.

Example:

dbadmin=> CREATE TABLE empty_string (es VARCHAR(10));
CREATE TABLE

dbadmin=> INSERT INTO empty_string SELECT '';
OUTPUT
--------
      1
(1 row)

dbadmin=> UPDATE empty_string SET es = REPLACE(es, '''', 'Replaced!');
OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT * FROM empty_string;
es
----

(1 row)

dbadmin=> UPDATE empty_string SET es = DECODE(es, '', 'Replaced!', '');
OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT * FROM empty_string;
    es
-----------
Replaced!
(1 row)

Have Fun!

jdani015

You can also replace using this: NULLIF( es, '' )

Jim_Knicely

Just like the REPLACE function, NULLIF won't treat an empty string as NULL.

Example:

dbadmin=> TRUNCATE TABLE empty_string;
TRUNCATE TABLE

dbadmin=> INSERT INTO empty_string SELECT '';
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT NULLIF(es, 'It''s NULL') FROM empty_string;
 NULLIF
--------

(1 row)

jdani015

Jim, you are not using the function correctly. NULLIF looks at the first argument and compares it with the second argument which is 'es' and '' (empty string) respectively. If 'es' contains an empty string then NULLIF(es, '') returns NULL.

Written this way illustrates: NVL(NULLIF(es, ''), 'It''s NULL') returns It's NULL

Jim_Knicely

Ok. I see what you are saying. So yup, this is another way to replace an empty string

dbadmin=> UPDATE empty_string SET es = 'REPLACED!' WHERE NULLIF(es, '' ) IS NULL;
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT * FROM empty_string;
    es
-----------
 REPLACED!
(1 row)

Or...

    dbadmin=> rollback;
    ROLLBACK

    dbadmin=> insert into empty_string select '';
     OUTPUT
    --------
          1
    (1 row)

    dbadmin=> UPDATE empty_string SET es = NVL(NULLIF(es, '' ), 'REPLACED!');
     OUTPUT
    --------
          1
    (1 row)

    dbadmin=> SELECT * FROM empty_string;
        es
    -----------
     REPLACED!
    (1 row)

jdani015

Isabelle_Vela

Does anybody know why these 2 values (NULL and empty string) have been implemented by the Vertica designers ? Is there a difference in the space used by NULL or empty columns in the DB?

Jim_Knicely

Vertica is following the ISO standards. For a great discussion on this topic see:

https://community.oracle.com/ideas/19866

I'm an ex-Oracle DBA and was surprised at first to see that Vertica treats an empty string vs a NULL. But now I'm used to it and prefer Vertica's method.

FYI ...

Vertica's license usage is the same for an empty string and a NULL... That is, both use ZERO license.

Example:

dbadmin=> CREATE TABLE test_empty_string(c VARCHAR(1));
CREATE TABLE

dbadmin=> INSERT /*+ DIRECT */ INTO test_empty_string SELECT '';
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT AUDIT('test_empty_string');
 AUDIT
-------
 0
(1 row)

dbadmin=> CREATE TABLE test_null(c VARCHAR(1));
CREATE TABLE

dbadmin=> INSERT /*+ DIRECT */ INTO test_null SELECT NULL;
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT AUDIT('test_null');
 AUDIT
-------
 0
(1 row)

And the storage space used is the same for an empty string and a NULL.

dbadmin=> SELECT anchor_table_name, row_count, used_bytes
dbadmin->   FROM projection_storage
dbadmin->  WHERE anchor_table_name IN ('test_empty_string', 'test_null');
 anchor_table_name | row_count | used_bytes
-------------------+-----------+------------
 test_empty_string |         1 |         75
 test_null         |         1 |         75
(2 rows)