Splitting up strings of data

[JimKnicely]

If you need to "split" a string into parts, there is a neat built-in function appropriately named SPLIT_PART which can help you do that.

The function has the syntax: SPLIT_PART ( string, delimiter, field ) where string is the argument string, delimiter is what separates the parts of the string and field is the number of the part you want returned.

Examples:

I can easily extract the street address at position 3:

Code: Select all

dbadmin=> SELECT SPLIT_PART('1|Phil Nickerson|1313 Appleton Street|Crave City|TX|76352', '|', 3) "Street Address";
    Street Address
----------------------
 1313 Appleton Street
(1 row)

And I can split a string up into the rows of a query result set:

Code: Select all

dbadmin=> SELECT SPLIT_PART('JIM|TOM|PATRICK|PENG|MARK|BRIAN', '|', row_num) "User Names"
dbadmin->   FROM (SELECT ROW_NUMBER() OVER () AS row_num
dbadmin(>           FROM tables) row_nums
dbadmin->  WHERE SPLIT_PART('JIM|TOM|PATRICK|PENG|MARK|BRIAN', '|', row_num) <> '';
 User Names
------------
 JIM
 TOM
 PATRICK
 PENG
 MARK
 BRIAN
(6 rows)

[rajasekhart]

Hi Knicely87,

Could you please help me to get the comma separated values in a single row (the exact reverse method of split_part()
function).

Eg: select name from Employees;
--The following results after executing the above statement.
name
raj
sekhar
nicely

so, my requirement is to get the names as follows.
name
raj,sekhar,nicely

Kindly help me if it is possible.

Thanks in advance,
Rajasekhar

[JimKnicely]

Rajasekhar,

In MySQL it's very easy to do that!

Example:

Code: Select all

(mydbadmin@localhost) [jim]> select * from combine;
+--------+
| name   |
+--------+
| raj    |
| sekhar |
| nicely |
+--------+
3 rows in set (0.00 sec)

(mydbadmin@localhost) [jim]> select group_concat(name) from combine;
+--------------------+
| group_concat(name) |
+--------------------+
| raj,sekhar,nicely  |
+--------------------+
1 row in set (0.00 sec)

Unfortunately, Vertica does not have the group_concat function natively.

One whacky solution in Vertica is the following:

Code: Select all

dbadmin=> select * from combine;
  name
--------
 raj
 sekhar
 nicely
(3 rows)

dbadmin=> select MAX(DECODE(rn, 1, name)) ||
dbadmin->          NVL(MAX(DECODE(rn, 2, ',' || name)), '') ||
dbadmin->          NVL(MAX(DECODE(rn, 3, ',' || name)), '') ||
dbadmin->          NVL(MAX(DECODE(rn, 4, ',' || name)), '') ||
dbadmin->          NVL(MAX(DECODE(rn, 5, ',' || name)), '') ||
dbadmin->          NVL(MAX(DECODE(rn, 6, ',' || name)), '') ||
dbadmin->          NVL(MAX(DECODE(rn, 7, ',' || name)), '') ||
dbadmin->          NVL(MAX(DECODE(rn, 8, ',' || name)), '') ||
dbadmin->          NVL(MAX(DECODE(rn, 9, ',' || name)), '') ||
dbadmin->          NVL(MAX(DECODE(rn, 10, ',' || name)), '') group_concate
dbadmin->   from (select row_number() over() rn, name from combine) foo;
   group_concate
-------------------
 raj,sekhar,nicely
(1 row)

If there are more than 10 members, you'll have to modify the code to add more handlers. This is a HUGE limitation of course. It's hard to know in advance how group members you'll have.

I hope this helps...

[becky]

Why would you ever need this in an analytic database? People are treating vertica like a traditional data store. Bad idea IMHO