Function for extracting delimited substrings in a string
Moderators: chulett, rschirm, roy
Function for extracting delimited substrings in a string
Hi,
We have a requirement wherein we need to extract substrings from a string where the delimiter is "^~"(basically delimiter is more than 1 character.)
Field function supports delimiter with a single character.
For example :-
input_string = A:B^~C:D^~E:F
We want to get individual strings A:B, C:D, E:F
Delimiter is "^~".
If anyone is aware of any such function that can be used to get delimited substrings where delimiter is more than a character, please let us know.
Thanks in advance.
We have a requirement wherein we need to extract substrings from a string where the delimiter is "^~"(basically delimiter is more than 1 character.)
Field function supports delimiter with a single character.
For example :-
input_string = A:B^~C:D^~E:F
We want to get individual strings A:B, C:D, E:F
Delimiter is "^~".
If anyone is aware of any such function that can be used to get delimited substrings where delimiter is more than a character, please let us know.
Thanks in advance.
How many columns? how many records ?
is the data format fixed and always like A:B^~C:M^~E:F ?? that is two charcter_1:character_2^~charcter_3:character_4. If you have fixed columns you can use the substring to extarct the data and concatenate them at the end. if the data is hefty why not write a routine/function do it?
is the data format fixed and always like A:B^~C:M^~E:F ?? that is two charcter_1:character_2^~charcter_3:character_4. If you have fixed columns you can use the substring to extarct the data and concatenate them at the end. if the data is hefty why not write a routine/function do it?
Teradata Certified Master V2R5
-
- Premium Member
- Posts: 232
- Joined: Fri Aug 04, 2006 1:20 am
- Location: Bangalore
Convert the ^~ to a single delimeter by using CONVERT and use the field function
Code: Select all
Convert("^~","|",InputLink.Col)
DS_SUPPORT wrote:Convert the ^~ to a single delimeter by using CONVERT and use the field functionCode: Select all
Convert("^~","|",InputLink.Col)
Thanks for the reply, but the reason for using multiple char as delimiter is that such type of combination of char (not exactly what is given in the example )may not be present in the production data.
In case of single char it will fail for the following example:-
input string :- A:B^C:^D^E:F
expected substrings :- A:B, C:^D, E:F
Delimiter :- ^
In the above example it will fail because sub strings returned would be :- A:B, C:, D, E:F
Apart from using multiple char as delimiter, instead we can use a character thats not present on the keyboard. Can this be achieved in DataStage?
Any pointers in this regard would be very helpful.
Thanks
You can use the Field() function....psluser wrote:Hi,hamzaqk wrote:are the smile coming through in your data too ?
Please consider the example as below for clarification:-
input_string = A:B^~C:M^~E:F
We want to get individual strings A:B, C:M, E:F
Delimiter is "^~".
Thanks,
Something like Field(input_string,"^~",2)
hope it helps...
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore