Function for extracting delimited substrings in a string

[psluser]

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.

[hamzaqk]

are the smiles coming through in your data too ?

[psluser]

are the smile coming through in your data too ?

Hi,

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,

[chulett]

A couple of tips...

Always 'Preview' your posts so you know what they will look like.

Next time you have this problem, enable the 'Disable Smilies in this post' option.

[hamzaqk]

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?

[DS_SUPPORT]

Convert the ^~ to a single delimeter by using CONVERT and use the field function

Code: Select all

Convert("^~","|",InputLink.Col)

[psluser]

Convert the ^~ to a single delimeter by using CONVERT and use the field function

Code: 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

[Mike]

You can convert to the system variable @FM as your single character delimiter. It's not on your keyboard.

Mike

[cedua]

are the smile coming through in your data too ?

Hi,

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,

You can use the Field() function....

Something like Field(input_string,"^~",2)

hope it helps...

[chulett]

Nope, 'fraid not. Field() only supports a single character delimiter, hence all of the other shenanigans people are discussing.

[kandyshandy]

Hello, Do you know how many delimiters will be there in a string? Is it fixed or variable?

[cedua]

JUST AN IDEA

It could be with "doble pass" field function..... (based on the example)