Parsing a String as Rows

[Deleted User]

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!

KWillets

You can also cross join to an explicit list of indices, regexes, etc.