Replacing an Empty String

SarahLSarahL Administrator, Moderator, Employee, Registered User, VerticaExpert, CABuser, VerticaPartners
edited May 31 in Vertica Tips

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!

Comments

  • jdani015jdani015 Registered User

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

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    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)
    
  • jdani015jdani015 Registered User

    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_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
    edited June 8

    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)
    
  • jdani015jdani015 Registered User
  • Isabelle_VelaIsabelle_Vela Registered User

    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_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
    edited November 22

    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)
    
Sign In or Register to comment.