Skip to main content

Replacing an Empty String

  • May 31, 2018
  • 7 replies
  • 19 views
Forum|alt.badge.img+2

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!

    7 replies

    jdani015
    • jdani015
    • New Participant
    • June 8, 2018

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

    Jim_Knicely
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • June 8, 2018

    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
    • jdani015
    • New Participant
    • June 8, 2018

    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
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • June 8, 2018

    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
    • jdani015
    • New Participant
    • June 8, 2018

    :)

    Isabelle_Vela
    Forum|alt.badge.img+1
    • Isabelle_Vela
    • Participating Frequently
    • November 22, 2018

    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
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • November 22, 2018

    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)
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement