The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Parsing a String as Rows
![[Deleted User]](https://us.v-cdn.net/6029397/uploads/defaultavatar/nD0LWW9MQTB29.jpg)
This tip was 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!
0
Comments
You can also cross join to an explicit list of indices, regexes, etc.