Regular expressions in Datastage Tranformer

guidomarcel1 · Post by **guidomarcel1** » Fri May 12, 2006 2:52 am

Hi,
I have the following situation:

I selected records from a table. The record contains a field called option_string which may contain 0-n time a 4byte option.

This means the field option_string may look like this:
471 DC12AS21RT 65T5FGV3
or like this:
LK12L23 DE3 SW2 AQ1 FR4 DE34RF45

Now, I have to separate the 4-byte options by a comma, so that it will look like this:
471 ,DC12,AS21,RT 6,5T5F,GV3
or like this:
LK12,L23 ,DE3 ,SW2 ,AQ1 ,FR4 ,DE34,RF45

Any way to do this via regular expression in Datastage transformer?
Thanks
GuidoMarcel

ArndW · Post by **ArndW** » Fri May 12, 2006 3:23 am

None of the builtin formatting options or other string function can do this in 1 line as would be necessary for a derivation in a transform stage.

I would put in a very short Routine that does:

Code: Select all

Routine MyRoutine(InString)
   Ans = ''
   StringLen = LEN(InString)
   FOR Index = 1 TO StringLen STEP 4
      Ans := InString[Index,4]:','  
   NEXT Index
   Ans = Ans[1,LEN(Ans)-1] ;** strip out last character

guidomarcel1 · Post by **guidomarcel1** » Fri May 12, 2006 4:02 am

Hi,
thank you very much. Knowing that it is not possible with the built-in functions is already of great help. My compan has no premium account. So, I can not read the rest of your answer. I will try myself!

Thx!
GuidoMarcel

ArndW · Post by **ArndW** » Fri May 12, 2006 4:22 am

Guido,

I have marked the post a non-premium so that you can take a look at a simple solution.

guidomarcel1 · Post by **guidomarcel1** » Fri May 12, 2006 4:31 am

thanks :D

ray.wurlod · Post by **ray.wurlod** » Fri May 12, 2006 5:43 am

The Fold() function will separate the string into four-character pieces separate by @FM characters, a Convert() function will do the rest.

Code: Select all

Convert(@FM, ",", Fold(InLink.TheColumn, 4))

guidomarcel1 · Post by **guidomarcel1** » Fri May 12, 2006 6:48 am

also thanks! :D

ArndW · Post by **ArndW** » Fri May 12, 2006 7:47 am

Ray - thanks for that. I went through the list of functions since I had a nagging memory that something like that existed and I couldn't find it; then I checked the formatting of ICONV/OCONV in case the function was hidden in there. I'm glad you remembered that one, since the one-liner without a routine call is much more efficient.

ray.wurlod · Post by **ray.wurlod** » Fri May 12, 2006 3:21 pm

You'll have to stop drinking that English beer!