Vertica Quick Tip: Replacing an Empty String

Posted May 31, 2018 by Soniya Shah, Information Developer

City in Blur Motion
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!