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:


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: 

get_value_varchar(array_split(ab_msisdn,'|'),1) source, get_value_varchar(array_split(ab_msisdn,'|'),2) target

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.


Popular posts from this blog

HIVE: Both Left and Right Aliases Encountered in Join

Assign select result to variable in Netezza stored procedure