Vertica Quick Tip: Parsing a String as Rows

This blog post was authored by Jim Knicely. A simple SQL trick makes it easy to expand an entire string into separate rows. Example: dbadmin=> SELECT substr('Vertica is Awesome!', x, 1) forward_string_parse dbadmin-> FROM (SELECT row_number() OVER () x FROM tables) foo dbadmin-> WHERE substr('Vertica is Awesome!', x, 1) <> ''; forward_string_parse ---------------------- V e r t i c a i s A w e s o m e ! (19 rows) Now that you’ve expanded the string into rows, you can perform various actions if you encounter certain conditions… dbadmin=> SELECT CASE substr('Vertica is Awesome!', x, 1) dbadmin-> WHEN 'A' THEN 'A - Found an "A"! That''s awesome!' dbadmin-> WHEN '!' THEN '! - Found an "!" Calm down, we all know Vertica is awesome :)' dbadmin-> ELSE substr('Vertica is Awesome!', x, 1) dbadmin-> END forward_string_parse dbadmin-> FROM (SELECT row_number() OVER () x FROM tables) foo dbadmin-> WHERE substr('Vertica is Awesome!', x, 1) <> ''; forward_string_parse --------------------------------------------------------------- V e r t i c a i s A – Found an “A”! That’s awesome! w e s o m e ! – Found an “!” Calm down, we all know Vertica is awesome 🙂 (19 rows) You can also do it in reverse! dbadmin=> SELECT CASE substr('Vertica is Awesome!', x, 1) dbadmin-> WHEN 'A' THEN 'A - Found an an "A"! That''s awesome!' dbadmin-> WHEN '!' THEN '! - Found an "!" Calm down, we all know Vertica is awesome :)' dbadmin-> ELSE substr('Vertica is Awesome!', x, 1) dbadmin-> END forward_string_parse dbadmin-> FROM (SELECT row_number() OVER () x FROM tables) foo dbadmin-> WHERE substr('Vertica is Awesome!', x, 1) <> '' dbadmin-> ORDER BY x DESC; forward_string_parse --------------------------------------------------------------- ! - Found an "!" Calm down, we all know Vertica is awesome :) e m o s e w A - Found an an "A"! That's awesome! s i a c i t r e V (19 rows) Have Fun!