Re: function to separate values from a column which are dot separated into n number of columns
The basic process of extracting different parts of a string from a string made up of several delimited parts is to extract the first part first. As you indicated that is the easy part. To extract the next part what you want is to start with a string that does not have the first part anymore but just the remaining parts. That is easy too since you just find the position of the first delimiter and the length of the string to find the substring start and end positions. So now you have the remaining string minus the first part. You have to give this remaining part a name. You use this remaining string as the input to extract the first part, which we already determined was easy to do then get the remaining part of that string minus the first part (really the second part of the original string) and name that. You keep repeating that process until all desired parts are extracted.
Of course now you have a bunch of extra parts. Each of the parts you extracted and the remaining intermediate strings, which you probably don’t want. So just put that process into a sub-query and just pick the parts you want to show or insert into and ignore the rest. Here is a sample:
Example BTEQ output:
.set sidetitles on .set foldline on
select b from st1;
*** Query completed. One row found. One column returned. *** Total elapsed time was 1 second.
select t.sp1, t.sp2, t.sp3, t.sp4 from (select substring(b from 1 for position('.' in b)-1) as sp1, substring(b from position('.' in b)+1 for character_length(b)) as sr1, substring(sr1 from 1 for position('.' in sr1)-1) as sp2, substring(sr1 from position('.' in sr1)+1 for character_length(sr1)) as sr2, substring(sr2 from 1 for position('.' in sr2)-1) as sp3, substring(sr2 from position( '.' in sr2)+1 for character_length(sr2)) as sp4 from st1) as t;
*** Query completed. One row found. 4 columns returned. *** Total elapsed time was 1 second.