[
https://issues.apache.org/jira/browse/DRILL-6968?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16895317#comment-16895317
]
benj commented on DRILL-6968:
-----------------------------
OK, I found that unnest is implemented even it's not really well documented. So
it's possible to do the split_to_table with combination of *split* and *unnest*.
Example :
{code:sql}
SELECT b, u FROM
(SELECT split(a,',') a, b FROM
(SELECT 'Drill,baby,drill' AS a, 1 AS b
UNION SELECT 'Drill,1.16' AS a, 2 AS b)
) x
,LATERAL (SELECT $unnest AS u FROM unnest(x.a));
+---+-------+
| b | u |
+---+-------+
| 2 | Drill |
| 2 | 1.16 |
| 1 | Drill |
| 1 | baby |
| 1 | drill |
+---+-------+
{code}
Please note that some SGBD (Postgres for example) proposes a simpler syntax for
_unnest_. It's possible to use it direcly as a function
([https://www.postgresql.org/docs/current/functions-array.html|https://www.postgresql.org/docs/current/functions-array.html)])
Example :
{code:sql}
/* Postgre syntax */
SELECT unnest(regexp_split_to_array(a,',')), b FROM
(SELECT 'Drill,baby,drill' AS a, 1 AS b
UNION SELECT 'Drill,1.16' AS a, 2 AS b) x
/* Outside of these example, use directly regexp_split_to_table (instead of
unnest(regexp_split_to_array)) */
{code}
> pattern matching and regexp facilities
> --------------------------------------
>
> Key: DRILL-6968
> URL: https://issues.apache.org/jira/browse/DRILL-6968
> Project: Apache Drill
> Issue Type: Wish
> Components: Functions - Drill
> Affects Versions: 1.15.0
> Reporter: benj
> Priority: Major
>
> Although it is possible to do some of them self, adding some possibilities
> from pattern matching directly in DRILL
> * may be usefull,
> * would prevent (re)write them and check again and again,
> * would allow a quicker and more effective handling of Drill.
> Some propositions :
> * adding possibilities to regexp_replace (ignoreCase,
> replaceAll(currently)/replaceOnlyFirst ...)
> * regexp_matches (lighter and more consistent than using regexp_replace
> trick)
> * split_to_table
> {noformat}
> SELECT split_to_table('Drill,baby,drill',',') AS mywords, 1 AS x;
> +---------+---+
> | mywords | x |
> +---------+---+
> | Drill | 1 |
> | baby | 1 |
> | drill | 1 |
> +---------+---+
> {noformat}
> * split_to_array
> {noformat}
> SELECT split_to_array('Drill,baby,drill',',') AS mywords, 1 AS x;
> +---------------------------+---+
> | mywords | x |
> +---------------------------+---+
> | ["Drill","baby","drill"] | 1 |
> +---------------------------+---+
> {noformat}
> * ...
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
