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