Using Field function to get final delimeter of a string

[vivek]

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.

[chulett]

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:

Code: Select all

Field(YourField,' ',Count(YourField,'  ')+1,1)

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'.

[ray.wurlod]

I have a Transform called FinalDelimitedString as follows

Code: Select all

Field(%Arg1%,%delimiter%,Count(%Arg1%,%delimiter%)+1,1)

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.

[chulett]

That's true about single spaces as legitimate data, didn't think about that. If that can happen, then Trim will work against your goal and you'll have to get your hands dirty writing a more complex routine.

[vivek]

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.

[nvkuriseti]

Vivek,

You can implement this logic by using INDEX and SUBSTRINGS functions to get the result. This is one of the clues.

Regards,
Venkat

[loveojha2]

Use DCount to get the number of occurences of double spaces.
Use Field to get the last field (which is no returned from DCount).

You don't need to write a function for that.

[chulett]

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).

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?

Code: Select all

znf..fre..fr.q..refd

If the answer is 'no', then a routine is not needed and the code that either of us posted will work fine with the addition of a trim around each occurance of the field:

Code: Select all

Field(Trim(YourField),' ',Count(Trim(YourField),' ')+1,1)

If the answer is 'yes' you will need to deal with something like that, then a routine would be more appropriate. You won't be able to use Field() there because of the double-character delimiters but the other suggestions should work out, I would think.

[kduke]

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 ''.

[kduke]

If you still need help then let us know.

[Mike]

Perhaps you might be able to use one of the BASIC string functions to change your 2-character delimiter to a single character delimiter (e.g. @FM), and then wrap it in the expression that Ray provided.

Mike

[chulett]

You mean like Trim()?

[Mike]

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

[chulett]

Yah, the nuances of this will be dictated by Vivek coming back and letting us know exactly what he could encounter in this delimited data.

[vivek]

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!