Using Field function to get final delimeter of a string
Moderators: chulett, rschirm, roy
Using Field function to get final delimeter of a string
Hi,
I have a requirement where the column iam working(froma flat file) with is having single and double spaces betwen some text data.
(please note that i have replaced each space with an underscore symbol for convenience)
example1: abc_2wer__edr_der__xyz
example2: 123__abc_def_rgv_123
the requirement is that i should get the data which comes after a the final double space.
So considering the example, i want to extract 'xyz' from the example1.
and 'abc' from example2.
question1.if we know the instance of the delimeter(such as 1,2 or 3 etc..) we can easily do this, but where we have to get the final instance is it possible to work with Field function?
question2:while i was trying to solve this problem, even though i specify the Double spaces within the quotes as delimeter(' ') the field function is reading only single space can any one explain?
Thanks in advance.
I have a requirement where the column iam working(froma flat file) with is having single and double spaces betwen some text data.
(please note that i have replaced each space with an underscore symbol for convenience)
example1: abc_2wer__edr_der__xyz
example2: 123__abc_def_rgv_123
the requirement is that i should get the data which comes after a the final double space.
So considering the example, i want to extract 'xyz' from the example1.
and 'abc' from example2.
question1.if we know the instance of the delimeter(such as 1,2 or 3 etc..) we can easily do this, but where we have to get the final instance is it possible to work with Field function?
question2:while i was trying to solve this problem, even though i specify the Double spaces within the quotes as delimeter(' ') the field function is reading only single space can any one explain?
Thanks in advance.
Use another function inline to count the number of delimiters in the data. Use that number + 1 as the instance of the field to extract:
That's off the top of my head, suggest you build a routine as a test harness to check it does what you want with your data consistantly.
Not sure if Field() supports multiple character delimiters, check the docs. Experiment. Or use Trim() to get things down to one space between 'fields'.
Code: Select all
Field(YourField,' ',Count(YourField,' ')+1,1)
Not sure if Field() supports multiple character delimiters, check the docs. Experiment. Or use Trim() to get things down to one space between 'fields'.
-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:
I have a Transform called FinalDelimitedString as follows
which is pretty much what Craig posted.
This does not solve the znf__fre__fr_q problem if you need fr_q as the result, since Field only uses single character delimiters. In this case you will need a routine, because what you want to do can not be done with a simple expression.
Code: Select all
Field(%Arg1%,%delimiter%,Count(%Arg1%,%delimiter%)+1,1)
This does not solve the znf__fre__fr_q problem if you need fr_q as the result, since Field only uses single character delimiters. In this case you will need a routine, because what you want to do can not be done with a simple expression.
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.
Ray, Chulette Thanks for the replies.
I was actually trying to write a routine to solve this problem.If I bring the spaces to a uniform single space delimeter how i am going to know about the last field which is separated by the double spaces?(as in the requirement).Can anybody give me a clue, how to proceed with a routine to achieve this?what exceptions should i give and what functions should i use in that routine?
Any help is appriciated.
I was actually trying to write a routine to solve this problem.If I bring the spaces to a uniform single space delimeter how i am going to know about the last field which is separated by the double spaces?(as in the requirement).Can anybody give me a clue, how to proceed with a routine to achieve this?what exceptions should i give and what functions should i use in that routine?
Any help is appriciated.
-
- Premium Member
- Posts: 38
- Joined: Sat Jan 17, 2004 12:29 am
- Location: Hamburg, Germany
You haven't said if a single space is legitimate data that can be found between delimiters. For example (using dots rather than underscores for spaces) will you see something like this in your data?vivek wrote:I was actually trying to write a routine to solve this problem.If I bring the spaces to a uniform single space delimeter how i am going to know about the last field which is separated by the double spaces?(as in the requirement).
Code: Select all
znf..fre..fr.q..refd
Code: Select all
Field(Trim(YourField),' ',Count(Trim(YourField),' ')+1,1)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
You can write your own with Index(). Index() will find any string within another string. It returns the position of where it found it. It is fairly fast because of how it works under the covers. Index() will look for the 3rd or 4th occurance of the string.
So you will need to find the position of the occurance before the one you really want and then position of the occurance of the one you want. Next build a substring out of these. So if you want the 5th field use Index() to find the 4th time this delimiter is found in your string. Next find the position of the 5th occurance. So if 4th = 10 and 5th = 15 then what you want is between 10 and 15 or MyString[10,6].
I did not think Dcount() would look for 2 spaces. If it does then you could use these in combination. If Dcount() is less than the field you are looking for then the result is ''.
So you will need to find the position of the occurance before the one you really want and then position of the occurance of the one you want. Next build a substring out of these. So if you want the 5th field use Index() to find the 4th time this delimiter is found in your string. Next find the position of the 5th occurance. So if 4th = 10 and 5th = 15 then what you want is between 10 and 15 or MyString[10,6].
I did not think Dcount() would look for 2 spaces. If it does then you could use these in combination. If Dcount() is less than the field you are looking for then the result is ''.
Mamu Kim
Yep. Trim() if a single space isn't expected to occur in the data. I was thinking something more along the lines of Convert() in order to change to a delimiter that wouldn't be likely to occur in the data. We still have problems though with 3,4,... consecutive space characters (if they can legitimately occur in the data).
Mike
Mike
Hi all,
First of all, I really appriciate your help and effort for this solution.
I have used 'Dcount' and 'Field' functions to attain this solution.
here is the code:
mystring=Arg1
d1=dcount(mystring,' ')
d2=d1-1
d3=index(mystring,' ',d2)
d4=mystring[d3+1,30]
d5=left(d4,3)
Ans=d5
once again thanks alot.
cheers!
First of all, I really appriciate your help and effort for this solution.
I have used 'Dcount' and 'Field' functions to attain this solution.
here is the code:
mystring=Arg1
d1=dcount(mystring,' ')
d2=d1-1
d3=index(mystring,' ',d2)
d4=mystring[d3+1,30]
d5=left(d4,3)
Ans=d5
once again thanks alot.
cheers!