Replacing an Empty String

[Deleted User][Deleted User] Administrator
edited May 2018 in Tips from the Team

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

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

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    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)
    
  • 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 - Select Field - Administrator
    edited June 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)
    
  • 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 - Select Field - Administrator
    edited November 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)
    
Sign In or Register to comment.