|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/23/2008 4:44:59 AM
Posts: 3,
Visits: 22
|
|
Hi
I have a column A in which have flexifield values ( xxxx.xxxxxxxx.xxxxxx.xxxxxxxx)
I need to get the values in Coulmn A into 4 different columns
columnB Column C Column D Column E
The table apart from the above mentioned 5 columns has 15-20 more columns in it .
Column A datatype is varchar(120).
Can someone please help me out to get the solution for this case.
I could get the first column usint substring and index funtions but other columns i.e C,D and E i am not able to generate separately
Thanks
Sirisha
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Today @ 7:30:07 AM
Posts: 197,
Visits: 611
|
|
Hello,
If the input column contains delimiter (in your example "."), you can write a UDF which takes 2 inputs i.e. Index and Input string and returns a value on that index.
Then you can use it as follows:
INSERT Table1 Values (ColB, ColC, ColD)
SELECT udfSubstringIndex(ColA, 0) AS ColB, udfSubstringIndex(ColA, 1) AS ColC, udfSubstringIndex(ColA, 2) AS ColD
FROM Table1;
Regards,
Adeel
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/21/2008 10:45:25 PM
Posts: 79,
Visits: 98
|
|
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.
b part1.part2.part3.part4
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.
sp1 part1
sp2 part2
sp3 part3
sp4 part4
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 11/26/2008 2:48:30 AM
Posts: 41,
Visits: 94
|
|
Hi,
Can u plz. share some knowledge on UDF.
Regards,
Subhash
|
|
|
|