Splitting value in Netezza using array_split

To split a column's value in Netezza you can use the array_split function.

For example if column AB_MSISDN have a value like "01212345679|019234567679" and we'd like to split this into A number and B number, we could use the below command in Netezza:

array_split(ab_msisdn,'|')

Doing the above will split the values into arrays. However you wouldn't be able to access the value directly. To do this, you use the get_value_varchar function. Example below: 

select
ab_msisdn,
get_value_varchar(array_split(ab_msisdn,'|'),1) source, get_value_varchar(array_split(ab_msisdn,'|'),2) target
from
telco_edgelist;

Of course one could argue that there are other ways to do this such as using a substring or regex. This is just another option.

For more details on the function above do visit IBM's website such this.

Comments

Popular posts from this blog

HIVE: Both Left and Right Aliases Encountered in Join

Assign select result to variable in Netezza stored procedure