Function
Moderators: chulett, rschirm, roy
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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".
Then use the Ereplace function to effect the substitution
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.
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.
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)
Code: Select all
If Result > ""
Then
NewResult = Ereplace(TheString, Result, '" "', 1, 1)
End
Code: Select all
Ereplace(TheString, MatchField(TheString, "0X'Mark'0X'Jason'0X", 3), '" "', 1, 1)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
... 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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle