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

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!

Comments

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

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.