Vertica Quick Tip: Replacing an Empty String

This blog post was authored by Jim Knicely. 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!