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

Post a Comment

Popular posts from this blog

How to use diff in UNIX

The following were taken from http://www.unixtutorial.org/2008/02/compare-text-files-using-diff/. All credits goes to them. If you need to compare two text files in Unix, you're mostly likely to use the diff command. Today I'll talk about the simplest scenario: you want to compare two files and understand if there are any differences. Suppose you have two files in /tmp directory: /tmp/1.txt: aaa bbb ccc ddd eee fff ggg and /tmp/2.txt: bbb c c ddd eee fff ggg hhh I have deliberately created them so short and simple - this way it's easier to explain how the comparison works. If there are no differences between the files, you will see no output, but if two text files are indeed different, all the text mismatches will be highlighted using the standard diff output: $ diff /tmp/1.txt /tmp/2.txt 1d0 c c 7a7 > hhh Lines like "1d0" and "3c2" are the coordinates and types of the differences between the two compared files, while lines like " " and ...
Keep reading

A Basic Recipe for Machine Learning

Image
Ever since wrapping up the three Deep Learning courses by Andrew Ng I've been meaning to write down some of the gems that he's highlighted throughout the course. One of the nice ones that I felt needed to be written down is his general recipe to approaching a deep learning algorithm/model. I've basically summarized it in a flowchart below (because everybody loves a flowchart right?) Basic deep learning recipe What is bias and variance? The below diagram is the typical explanation that I'm sure most of us are used to.  Graphical illustration of bias and variance How can we know if we have high bias or high variance?  For high bias, we could take a look at the training set performance. A poor performance is an indicator of a poor model fit, and signals that we could try to apply a bigger network to get a better fit of the model. For high variance, we could take a look at the validation set (or dev set - as Andrew calls it) performance. ...
Keep reading

HIVE: Both Left and Right Aliases Encountered in Join

Recently was stuck on this error while trying to do a JOIN in HIVE. Below is my SQL create table ma.internet_v2 as select A.msisdn, A.imsi, A.dt, A.start_time, A.end_time, A.url, A.ttl_connection_dur_ms, A.ttl_upload_bytes, A.ttl_download_bytes, A.ttl_cdr_cnt, coalesce(B.domain_desc,A.domain_desc) domain_desc, coalesce(B.subdomain_desc, A.subdomain_desc) subdomain_desc from db.internet A left outer join hfz_domain_name_mapping B on instr(A.url, B.url_pattern) > 0; HIVE then give me and error message "Both Left and Right Aliases Encountered in Join". After going through some mail threads it seems that HIVE can support certain types of join. Only equality joins, outer joins, and left semi joins are supported in Hive. Hive does not support join conditions that are not equality conditions as it is very difficult to express such conditions as a map/reduce job. A poster from  Stack Overflow  suggested that one should use WHERE instead of ON. Duly following his advice,...
Keep reading