We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Parsing a String as Rows — Vertica Forum

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.