Function

[Mark j]

Hi

Is there any function to do following simple task

I have an input : Mark?????Jason i want to make it Mark" "Jason


Thanks
Mark

[chulett]

Mark - you might want to clarify exactly what you it is you need to do before people go off chasing too many wild geese.

So... are you saying you have a field with text that can be seperated by any number (or perhaps a fixed number) of question marks or spaces or what? And then you need to turn it into what exactly - something with a single space between it or literally what you posted: a space surrounded by two double-quotes?

If you don't mind me asking, why would you need to do this? It seems like an... "interesting" requirement.

[ray.wurlod]

This is an easy task for DataStage's pattern matching capabilities.

You need to determine what's between "Mark" and "Jason" and then replace this with the three characters '" "' (a double quote, a space and another double quote).

Use the MatchField function to get what's between "Mark" and "Jason".

Code: Select all

Pattern = "0X'Mark'0X'Jason'0X"
Result = MatchField(TheString, Pattern, 3)

Then use the Ereplace function to effect the substitution

Code: Select all

If Result > ""
Then
   NewResult = Ereplace(TheString, Result, '" "', 1, 1)
End

If you want to do the whole thing as a single expression, for example for a column derivation, and you are sure of at least one character between "Mark" and "Jason", simply nest the functions.

Code: Select all

Ereplace(TheString, MatchField(TheString, "0X'Mark'0X'Jason'0X", 3), '" "', 1, 1)

If it's possible that there may be zero characters between "Mark" and "Jason", wrap it in an If .. Then .. Else to test for this possibility.

[chulett]

Or never mind the clarification and wait for Ray to roll you up an all-purpose answer.

[vmcburney]

Try not to put this type of code directly in your transformer, make it a routine and pass it values. That way you get to use the routine "Test" form to test various input patterns to make sure your logic is correct. You can then play around with lots of different EREPLACE and pattern options and test each one until it is correct, much faster then putting the code into a transformer and running a job to see if it correct.

[ray.wurlod]

... and the Test grid for Routines is a great tool for doing just that!

Folks to whom I teach DataStage classes will recount how I use this technique to help get a feel for the intricacies of Oconv, for example.

Good catch, Vince.

[Mark j]

Thanks for your suggestions

I phrased my question wrong:)

if i have more than one space it should be replaced with single space as in following example

Mark jason Oakman then the result should be Mark jason Oakman

you never know for sure how many spaces you have... I hope i am clear enough with my question.

Thanks
Mark

[kcbland]

Use of TRIM(link.column) removes all leading and trailing spaces; and where multiple spaces reduces to one space as long as it's not a leading or trailing space. Check your Online Documentation DS BASIC manual for the TRIM function.

[chulett]

Thanks for your suggestions. I phrased my question wrong:)

See, Ray. So how was that goose?